--Setting the logic version
update meta_version set minor = 3; --Do not forget to update the installer version in code

go

--0----------------------------------------------------------

--Creating all table columns view
create view meta_table_columns
as select c.table_id, table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
   'meta_string_columns' as column_type
from meta_string_columns c inner join meta_tables t
on c.table_id = t.table_id
union
select c.table_id, table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_ref_columns' as column_type
from meta_ref_columns c inner join meta_tables t
on c.table_id = t.table_id
union
select c.table_id, table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_enum_columns' as column_type
from meta_enum_columns c inner join meta_tables t
on c.table_id = t.table_id
union
select c.table_id, table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_decimal_columns' as column_type
from meta_decimal_columns c inner join meta_tables t
on c.table_id = t.table_id
union
select c.table_id, table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_datetime_columns' as column_type
from meta_datetime_columns c inner join meta_tables t
on c.table_id = t.table_id
union
select c.table_id, table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_bool_columns' as column_type
from meta_bool_columns c inner join meta_tables t
on c.table_id = t.table_id
union
select c.table_id, table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_binary_columns' as column_type
from meta_binary_columns c inner join meta_tables t
on c.table_id = t.table_id
union
select c.table_id, table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_int_columns' as column_type
from meta_int_columns c inner join meta_tables t
on c.table_id = t.table_id
union
select c.table_id, table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_big_int_columns' as column_type
from meta_big_int_columns c inner join meta_tables t
on c.table_id = t.table_id
union
select c.table_id, table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_small_int_columns' as column_type
from meta_small_int_columns c inner join meta_tables t
on c.table_id = t.table_id
union
select c.table_id, table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_guid_columns' as column_type
from meta_guid_columns c inner join meta_tables t
on c.table_id = t.table_id
union
select c.table_id, table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_text_columns' as column_type
from meta_text_columns c inner join meta_tables t
on c.table_id = t.table_id;

go

--Table of user names
create view meta_user_names
as select user_name, display_name from meta_users;

go

--Current user properties
create view meta_current_user
as select * from meta_users where user_name = lower(user);

go

--Table of security principals
create view meta_principals
as
select user_name as principal_name, display_name, 'user' as principal_type
from meta_users
union
select role_name as principal_name, display_name, 'role' as principal_type
from meta_roles;

go

--A view to show user's acl unit-level access rights
create view meta_user_acl_unit_access
as select table_id, unit_id, access_type from meta_table_acl_unit_access
where user_name = lower(user);

go

--A view to show user's acl full access rights
create view meta_user_acl_full_access
as select table_id, access_type from meta_table_acl_full_access
where user_name = lower(user);

go

--A view to show user's acl own rows access rights
create view meta_user_acl_own_rows
as select table_id, access_type from meta_table_acl_own_rows
where user_name = lower(user);

go

--1----------------------------------------------------------
if exists(select 1
        from information_schema.routines
        where routine_type = 'PROCEDURE'
        and routine_name = 'p_drop_proc_if_exists')
        exec ('drop procedure p_drop_proc_if_exists');

go

--Function of dropping a procedure
create procedure p_drop_proc_if_exists(
    --Target procedure name
    @a_proc_name varchar(255))
as
begin	
    if exists(select 1
        from information_schema.routines
        where routine_type = 'PROCEDURE'
        and routine_name = @a_proc_name)
        exec ('drop procedure ' + @a_proc_name);
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_func_if_exists';

go

--Function of dropping a function
create procedure p_drop_func_if_exists(
    --Target function name
    @a_func_name varchar(255))
as
begin
    if exists(select 1
        from information_schema.routines
        where routine_type = 'FUNCTION'
        and routine_name = @a_func_name)
        exec ('drop function ' + @a_func_name);
end;

go

exec dbo.p_drop_func_if_exists 'p_bin_to_str';

go

--Function of converting binary(16) to string
create function p_bin_to_str(
     @binvalue binary(16))
returns varchar(32)
as
begin
    declare @charvalue varchar(32);
    declare @i int;
    declare @length int;
    declare @hexstring char(16);

    set @charvalue = '';
    set @i = 1;
    set @hexstring = '0123456789abcdef';
    set @length = datalength(@binvalue);

    while (@i <= @length)
    begin
        declare @tempint int
        declare @firstint int
        declare @secondint int

        set @tempint = convert(int, substring(@binvalue,@i,1))
        set @firstint = floor(@tempint/16)
        set @secondint = @tempint - (@firstint*16)

        set @charvalue = @charvalue +
        substring(@hexstring, @firstint+1, 1) +
        substring(@hexstring, @secondint+1, 1)

        set @i = @i + 1
    end;
    
    return @charvalue;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_get_dbms_name';

go

--Function that gets dbms name
create procedure meta_get_dbms_name(
    @a_dbms_name varchar(64) output)
with execute as owner
as
begin
    set nocount on;
    select @a_dbms_name = 'SQLSERVER';
end;

go

exec dbo.p_drop_proc_if_exists 'meta_get_dbms_version';

go

--Function that gets dbms version
create procedure meta_get_dbms_version(
    @a_dbms_version varchar(64) output)
with execute as owner
as
begin
    set nocount on;
    select @a_dbms_version = convert(varchar(64), serverproperty('productversion'));
end;

go

exec dbo.p_drop_proc_if_exists 'meta_get_current_db_name';

go

--Function that return database name
create procedure meta_get_current_db_name(
    @a_db_name varchar(64) output)
with execute as owner
as
begin
    set nocount on;
    select @a_db_name = db_name();
end;

go

exec dbo.p_drop_func_if_exists 'p_get_table_name';

go

--Function of getting table name
create function p_get_table_name(
    --Table id
    @a_table_id binary(16))
    returns varchar(26)
as
begin
    --Table name
    declare @target_table_name varchar(30);
    
    if @a_table_id is null
    begin
        return null;
    end;

    select @target_table_name = table_name
    from meta_tables where table_id = @a_table_id;
    
    return @target_table_name;
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_table_if_exists';

go

--Function of dropping a table
create procedure p_drop_table_if_exists(
    --Target table name
    @a_table_name varchar(30))
as  
begin
    declare @table_count integer;
    
    select @table_count = count(*) from information_schema.tables
    where table_name = lower(@a_table_name)
    and table_type = 'BASE TABLE';
    
    if @table_count > 0
        execute ('drop table ' + @a_table_name);
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_view_if_exists';

go

--Function of dropping a view
create procedure p_drop_view_if_exists(
    --Target view name
    @a_view_name varchar(30))
as
begin
    declare @view_count integer;
    
    select @view_count = count(*) from information_schema.tables
    where table_name = lower(@a_view_name)
    and table_type = 'VIEW';
    
    if @view_count > 0
        execute ('drop view ' + @a_view_name);
end;

go

exec dbo.p_drop_proc_if_exists 'meta_soft_delete_table_row';

go

--Function of soft deleting a table row
create procedure meta_soft_delete_table_row(
	--Table id
	@a_table_id binary(16),
	--Row id
	@a_row_id binary(16))
with execute as owner
as
begin
	declare
	--Current row id
	@a_curr_row_id binary(16),
	--Current table id
	@a_curr_table_id binary(16),
    --Current table name
    @a_curr_table_name varchar(26);
    
    set nocount on;
    
    set xact_abort on;
    begin tran;
    
    --A table that stores rows to be deleted
    if object_id('tempdb..##rows_to_be_soft_deleted') is null --Creating a #temp table is slow
		create table ##rows_to_be_soft_deleted(
			--Table id
			table_id binary(16) not null,
			--Table name
			table_name varchar(26) not null,
			--Row id
			row_id binary(16) primary key);
		
	--A table that stores deleted rows
	declare @deleted_rows table (
		--Row id
		row_id binary(16) primary key);
	
	--Saying that we want to delete the initial row
	set @a_curr_table_name = dbo.p_get_table_name(@a_table_id);		
	insert ##rows_to_be_soft_deleted values(@a_table_id, @a_curr_table_name, @a_row_id);

    while (exists(select 1 from ##rows_to_be_soft_deleted))
    begin
		--Getting a row to be deleted
		select top 1 
			@a_curr_table_id = table_id,
			@a_curr_table_name = table_name,
			@a_curr_row_id = row_id
		from ##rows_to_be_soft_deleted;
		
		delete ##rows_to_be_soft_deleted
		where row_id = @a_curr_row_id;
		
		--If the current row is not deleted already	
		if not exists (select 1 from @deleted_rows where row_id = @a_curr_row_id)
		begin
		
			declare
			--Ref table id
			@a_ref_table_id binary(16),
			--Ref table name
			@a_ref_table_name varchar(26),
			--Ref column name
			@a_ref_column_name varchar(26),
			--Row count
			@a_row_count int;
			 
			--Enumerating all references on the current table
			declare @ref_cursor cursor;
			set @ref_cursor = cursor fast_forward read_only for
			select column_id, cascade_delete from meta_ref_targets
			where ref_table_id = @a_curr_table_id;
			 
			declare @a_ref_column_id binary(16),
			@a_ref_cascade_delete numeric(1);

			open @ref_cursor;
			fetch next from @ref_cursor into @a_ref_column_id, @a_ref_cascade_delete;

			while @@fetch_status = 0
			begin     
				--Reading ref column properties
				select @a_ref_column_name = column_name,
				@a_ref_table_id = table_id
				from dbo.meta_ref_columns
				where column_id = @a_ref_column_id;

				set @a_ref_table_name = dbo.p_get_table_name(@a_ref_table_id);

				--If cascade delete is forbidden
				if @a_ref_cascade_delete = 0
				begin
					--Checking reference value existence
					declare @check_ref_existence_sql nvarchar(500);
					set @check_ref_existence_sql = 'select @row_count_out = 1 where exists(select 1 from tbl_' + @a_ref_table_name
					+ ' where ' + @a_ref_column_name + ' = @row_id and is_active = 1)';

					exec sp_executesql @check_ref_existence_sql,
					N'@row_id binary(16), @row_count_out int output', 
					@row_id = @a_curr_row_id,
					@row_count_out = @a_row_count output;	

					--If there are references on the current row
					if @a_row_count > 0
					begin
						declare @a_row_id_str varchar(32);
						set @a_row_id_str = dbo.p_bin_to_str(@a_row_id);
					
						raiserror('Deleting when there are references on the row (id = %s) with CascadeDelete = false (#error_code = 20001)',
						16, 1, @a_row_id_str);
						rollback;
						return;
					end;
				end;
	    
				--Deleting other reference values too (putting them in the list of row to be deleted)
				declare @delete_other_rows_sql nvarchar(500);
				set @delete_other_rows_sql = 'insert into ##rows_to_be_soft_deleted(row_id, table_id, table_name)'
				+ ' select id, @table_id, @table_name from tbl_' + @a_ref_table_name
				+ ' where ' + @a_ref_column_name + ' = @row_id'
				+ ' and id not in (select row_id from ##rows_to_be_soft_deleted)';

				exec sp_executesql @delete_other_rows_sql,
				N'@row_id binary(16), @table_id binary(16), @table_name varchar(26)', 
				@row_id = @a_curr_row_id,
				@table_id = @a_ref_table_id,
				@table_name = @a_ref_table_name;

				fetch next from @ref_cursor into @a_ref_column_id, @a_ref_cascade_delete;
			end;
			 
			close @ref_cursor;
			deallocate @ref_cursor;
		
			--Deleting the current row
			declare @soft_delete_row_sql nvarchar(500);
			set @soft_delete_row_sql = 'update tbl_' + @a_curr_table_name
			+ ' set is_active = 0, deleted_by = lower(original_login()), deleted_on = getdate() where id=@row_id';
			
			exec sp_executesql @soft_delete_row_sql,
			N'@row_id binary(16)', 
			@row_id = @a_curr_row_id;
			
			--Adding the current row to the deleted rows list
			insert @deleted_rows values(@a_curr_row_id);
		end;
    end;
    
    --Not dropping #temp tables
	
	commit;
end;


go

exec dbo.p_drop_proc_if_exists 'meta_restore_table_row';

go

--Function of restoring a table row
create procedure meta_restore_table_row(
	--Table id
	@a_table_id binary(16),
	--Row id
	@a_row_id binary(16))
with execute as owner
as
begin
	declare
	--Current row id
	@a_curr_row_id binary(16),
	--Current table id
	@a_curr_table_id binary(16),
    --Current table name
    @a_curr_table_name varchar(26);
    
    set nocount on;
    
    set xact_abort on;
    begin tran;
    
    --A table that stores rows to be restored
    if object_id('tempdb..##rows_to_be_restored') is null --Creating a #temp table is slow
		create table ##rows_to_be_restored(
			--Table id
			table_id binary(16) not null,
			--Table name
			table_name varchar(26) not null,
			--Row id
			row_id binary(16) primary key);
		
	--A table that stores restored rows
	declare @restored_rows table (
		--Row id
		row_id binary(16) primary key);
	
	--Saying that we want to restore the initial row
	set @a_curr_table_name = dbo.p_get_table_name(@a_table_id);		
	insert ##rows_to_be_restored values(@a_table_id, @a_curr_table_name, @a_row_id);

    while (exists(select 1 from ##rows_to_be_restored))
    begin
		--Getting a row to be restored
		select top 1 
			@a_curr_table_id = table_id,
			@a_curr_table_name = table_name,
			@a_curr_row_id = row_id
		from ##rows_to_be_restored;
		
		delete ##rows_to_be_restored
		where row_id = @a_curr_row_id;
		
		--If the current row is not restored already	
		if (not exists(select 1 from @restored_rows where row_id = @a_curr_row_id))
		begin
			declare
			--Ref table name
			@a_ref_table_name varchar(26),
			--Row count
			@a_row_count int,
			--Reference value
			@a_ref_value binary(16),
			--Reference value was found
			@a_ref_value_was_found numeric(1);
		
			--Enumerating all references in this table
			declare @ref_column_id binary(16),
			@ref_column_name varchar(26),
			@ref_cursor cursor;

			set @ref_cursor = cursor fast_forward read_only for
			select column_id, column_name from meta_ref_columns
			where table_id = @a_curr_table_id;

			open @ref_cursor;
			fetch next from @ref_cursor into @ref_column_id, @ref_column_name;

			while @@fetch_status = 0
			begin
				--Reading reference value
				declare @read_ref_sql nvarchar(500);
				set @read_ref_sql = 'select @ref_value_out = ' + @ref_column_name + ' from tbl_'
				+ @a_curr_table_name + ' where id = @a_id';
				
				exec sp_executesql @read_ref_sql,
				N'@ref_value_out binary(16) output, @a_id binary(16)', 
				@a_id = @a_curr_row_id,
				@ref_value_out = @a_ref_value output;
			     
				if @a_ref_value is not null
				begin		     
					set @a_ref_value_was_found = 0;
			        
					--Enumerating all referenced tables
					declare @ref_table_id binary(16),
					@ref_table_cursor cursor;
					set @ref_table_cursor = cursor fast_forward read_only for
					select ref_table_id from meta_ref_targets
					where column_id = @ref_column_id;
			        
					open @ref_table_cursor;
					fetch next from @ref_table_cursor into @ref_table_id;
			         
					while @@fetch_status = 0
					begin                
						set @a_ref_table_name = dbo.p_get_table_name(@ref_table_id);
			     
						--Checking reference value existence
						declare @check_ref_sql nvarchar(500);
						set @check_ref_sql = 'select @a_row_count_out = 1 where exists(select 1 from tbl_'
						+ @a_ref_table_name
						+ ' where id = @a_ref_value)';
			            
						exec sp_executesql @check_ref_sql,
						N'@a_row_count_out int output, @a_ref_value binary(16)', 
						@a_ref_value = @a_ref_value,
						@a_row_count_out = @a_row_count output;
			     
						--If there are reference values
						if @a_row_count > 0
						begin
							--Restoring other reference values too (putting them in the list)
							declare @restore_other_rows_sql nvarchar(500);							
							set @restore_other_rows_sql = 'insert into ##rows_to_be_restored(row_id, table_id, table_name)'
							+ ' select @row_id, @table_id, @table_name'
							+ ' where @row_id not in (select row_id from ##rows_to_be_restored)';

							exec sp_executesql @restore_other_rows_sql,
							N'@row_id binary(16), @table_id binary(16), @table_name varchar(26)', 
							@row_id = @a_ref_value,
							@table_id = @ref_table_id,
							@table_name = @a_ref_table_name;
			            
							set @a_ref_value_was_found = 1;
						end;
			            
						fetch next from @ref_table_cursor into @ref_table_id;
					 end;
			         
					 close @ref_table_cursor;
					 deallocate @ref_table_cursor;
			         
					 --If reference value was not found
					 if @a_ref_value_was_found = 0
					 begin
						--Row id str
						declare @a_row_id_str varchar(32);
						--Ref value str
						declare @a_ref_value_str varchar(32);

						set @a_row_id_str = dbo.p_bin_to_str(@a_row_id);
						set @a_ref_value_str = dbo.p_bin_to_str(@a_ref_value);

						raiserror ('Restoring when there is no referenced row (id = %s, ref_value = %s) (#error_code = 20002)',
						16, 1, @a_row_id_str, @a_ref_value_str);
						rollback;
						return;
					 end;                
				 end;
			     
				 fetch next from @ref_cursor into @ref_column_id, @ref_column_name;
			 end;
			 
			close @ref_cursor;
			deallocate @ref_cursor;
		
			--Restoring the current row
			declare @soft_delete_row_sql nvarchar(500);
			set @soft_delete_row_sql = 'update tbl_' + @a_curr_table_name
			+ ' set is_active = 1, deleted_by = null, deleted_on = null where id=@row_id';
			
			exec sp_executesql @soft_delete_row_sql,
			N'@row_id binary(16)', 
			@row_id = @a_curr_row_id;
			
			--Adding the current row to the restored rows list
			insert @restored_rows values(@a_curr_row_id);
		end;
    end;
    
    --Not dropping #temp tables
	
	commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_table_row';

go

--Function of deleting a table row
create procedure meta_delete_table_row(
	--Table id
	@a_table_id binary(16),
	--Row id
	@a_row_id binary(16))
with execute as owner
as
begin
	declare
	--Current row id
	@a_curr_row_id binary(16),
	--Current table id
	@a_curr_table_id binary(16),
    --Current table name
    @a_curr_table_name varchar(26);
    
    set nocount on;
    
    set xact_abort on;
    begin tran;
    
    --A table that stores rows to be deleted
    if object_id('tempdb..##rows_to_be_deleted') is null --Creating a #temp table is slow
		create table ##rows_to_be_deleted(
			--Row id
			row_id binary(16) primary key,
			--Table id
			table_id binary(16) not null);
		
	--A table that stores deleted rows
	declare @deleted_rows table (
		--Row id
		row_id binary(16) primary key);
	
	--Saying that we want to delete the initial row
	set @a_curr_table_name = dbo.p_get_table_name(@a_table_id);		
	insert ##rows_to_be_deleted(row_id, table_id)
	values(@a_row_id, @a_table_id);

    while (exists(select 1 from ##rows_to_be_deleted))
    begin
   
		--Getting a row to be deleted
		select top 1 
			@a_curr_table_id = table_id,
			@a_curr_row_id = row_id
		from ##rows_to_be_deleted;
		
		set @a_curr_table_name = dbo.p_get_table_name(@a_curr_table_id);
		
		delete ##rows_to_be_deleted
		where row_id = @a_curr_row_id;
		
		--If the current row is not deleted already	
		if (not exists(select 1 from @deleted_rows where row_id = @a_curr_row_id))
		begin
		
			declare
			--Ref table id
			@a_ref_table_id binary(16),
			--Ref table name
			@a_ref_table_name varchar(26),
			--Ref column name
			@a_ref_column_name varchar(26),
			--Row count
			@a_row_count int;
			 
			--Enumerating all references on the current table
			declare @ref_cursor cursor;
			set @ref_cursor = cursor fast_forward read_only for
			select column_id, cascade_delete from meta_ref_targets
			where ref_table_id = @a_curr_table_id;
			 
			declare @a_ref_column_id binary(16),
			@a_ref_cascade_delete numeric(1);

			open @ref_cursor;
			fetch next from @ref_cursor into @a_ref_column_id, @a_ref_cascade_delete;

			while @@fetch_status = 0
			begin  
			
				--Reading ref column properties
				select @a_ref_column_name = column_name,
				@a_ref_table_id = table_id
				from dbo.meta_ref_columns
				where column_id = @a_ref_column_id;

				set @a_ref_table_name = dbo.p_get_table_name(@a_ref_table_id);
	    
				--Deleting other reference values too (putting them in the list of row to be deleted)
				declare @delete_other_rows_sql nvarchar(500);
				set @delete_other_rows_sql = 'insert into ##rows_to_be_deleted(row_id, table_id)'
				+ ' select id, @table_id from tbl_' + @a_ref_table_name
				+ ' where ' + @a_ref_column_name + ' = @row_id'
				+ ' and id not in (select row_id from ##rows_to_be_deleted)';

				exec sp_executesql @delete_other_rows_sql,
				N'@row_id binary(16), @table_id binary(16)', 
				@row_id = @a_curr_row_id,
				@table_id = @a_ref_table_id;
				
				fetch next from @ref_cursor into @a_ref_column_id, @a_ref_cascade_delete;
			end;
			 
			close @ref_cursor;
			deallocate @ref_cursor;
		
			--Deleting the current row
			declare @soft_delete_row_sql nvarchar(500);
			set @soft_delete_row_sql = 'delete from tbl_' + @a_curr_table_name
			+ ' where id=@row_id';
			
			exec sp_executesql @soft_delete_row_sql,
			N'@row_id binary(16)', 
			@row_id = @a_curr_row_id;
			
			--Adding the current row to the deleted rows list
			insert @deleted_rows values(@a_curr_row_id);
		end;
    end;
    
    --Not dropping #temp tables
	
	commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_table_triggers';

go

--Function of creating table triggers
create procedure p_create_table_triggers(
    --Table id
    @a_table_id binary(16))
as
begin
    declare
    --Table name
    @a_table_name varchar(26);
    --Table id str
    declare @a_table_id_str varchar(32);
    set @a_table_id_str = dbo.p_bin_to_str(@a_table_id);
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);    

    --Trigger for inserting a row
    execute ('create trigger i'
    + @a_table_id_str
    + ' on tbl_' + @a_table_name
    + ' instead of insert'
    + ' as begin'
    + ' set nocount on;'
    + ' select * into #inserted from inserted;'
    + ' update #inserted set id = convert(binary(16), newid()) where id is null;'
    + ' update #inserted set owner = lower(original_login()) where owner is null;'
    + ' update #inserted set owner = lower(owner),'
    + ' created_on = getdate(), updated_on = getdate(),'
    + ' created_by = lower(original_login()), updated_by = lower(original_login()),'
    + ' row_version = convert(binary(16), newid());'
    + ' insert into tbl_' + @a_table_name + ' select * from #inserted;'
    + ' drop table #inserted;'
    + ' end;');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_table_triggers';

go

--Function of dropping table triggers
create procedure p_drop_table_triggers(
    --Table id
    @a_table_id binary(16))
as
begin
    declare
    --Table id str
    @a_table_id_str varchar(32);
    
    set @a_table_id_str = dbo.p_bin_to_str(@a_table_id);
    
    exec ('drop trigger i' + @a_table_id_str);
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_table_view';

go

--Function of creating table view
create procedure p_create_table_view(
    --Table id
    @a_table_id binary(16))
as
begin
	declare
	--Recycle bin option
    @a_recycle_bin_option numeric(10),
	--Table name
    @table_name varchar(26),
    --Unit level security
    @a_unit_level_security numeric(1),
    --Comma delimited list of column names
    @column_name_list varchar(max);
    --Table id str
	declare @a_table_id_str varchar(32);
	
	set @column_name_list = '';
	set @a_table_id_str = dbo.p_bin_to_str(@a_table_id);  
	  
    set @table_name = dbo.p_get_table_name(@a_table_id);
    
    --Reading table properties
    select @a_unit_level_security = unit_level_security
    from meta_tables
    where table_id = @a_table_id;
    
    --Reading options
    select @a_recycle_bin_option = int_value
    from meta_options
    where option_name = 'RECYCLE_BIN';
    
    --Reading table columns
    declare @col_column_name varchar(26),
    @col_cursor cursor;
    set @col_cursor = cursor fast_forward read_only for
    select column_name from meta_table_columns
    where table_id = @a_table_id
    and column_name <> 'is_active'
    and column_name <> 'deleted_by'
    and column_name <> 'deleted_on'
    order by order_index;
    
    open @col_cursor;
    fetch next from @col_cursor into @col_column_name;
    
    while @@fetch_status = 0
    begin   
		if len(@column_name_list) > 0
			set @column_name_list = @column_name_list + ',';
            
        set @column_name_list = @column_name_list + @col_column_name;
		
		fetch next from @col_cursor into @col_column_name;
    end;
    
    close @col_cursor;
    deallocate @col_cursor; 

    --Creating the view
    declare @a_create_view_sql varchar(max);
    
    set @a_create_view_sql = 'create view '
    + @table_name
    + ' as select '
    + @column_name_list + ' from tbl_'
    + @table_name + ' t'
    + ' where 1 = 1';
    
    --If recycle bin is enabled
    if @a_recycle_bin_option = 1
        set @a_create_view_sql = @a_create_view_sql + ' and is_active = 1';
    
    --If unit level security is ON
    if @a_unit_level_security = 1
		set @a_create_view_sql = @a_create_view_sql
		--The full access
		+ ' and (exists(select 1 from meta_table_acl_full_access where table_id=0x'
		+ @a_table_id_str + ' and user_name=lower(original_login()) and access_type=1)'
		--The unit level access
		+ ' or t.owner in (select u.user_name from meta_table_acl_unit_access s'
		+ ' inner join meta_user_units u on s.unit_id=u.unit_id'
		+ ' where table_id=0x' + @a_table_id_str 
		+ ' and s.user_name=lower(original_login()) and access_type=1)'
		--The own rows access
		+ ' or t.owner=lower(original_login()) and exists(select 1 from meta_table_acl_own_rows'
		+ ' where table_id=0x'
        + @a_table_id_str + ' and user_name=lower(original_login()) and access_type=1))';
    exec (@a_create_view_sql);
    
    --Creating delete trigger
    declare @a_delete_trigger_sql varchar(max);
    
    set @a_delete_trigger_sql = 'create trigger d'
    + @a_table_id_str
    + ' on ' + @table_name
    + ' with execute as owner'
    + ' instead of delete as begin'
    + ' set nocount on;'
    + ' declare @a_row_id binary(16), @a_owner varchar(26),'
    + ' @a_has_full_rights numeric,'
    + ' @a_value_cursor cursor;'
      --Checking for full access rights
    + ' select @a_has_full_rights = count(*) from meta_table_acl_full_access where table_id=0x'
	+     @a_table_id_str + ' and user_name=lower(original_login()) and access_type=4;'
    + ' set @a_value_cursor = cursor fast_forward read_only for select id, owner from deleted;'
	+ ' open @a_value_cursor;'
	+ ' fetch next from @a_value_cursor into @a_row_id, @a_owner;'
	+ ' while @@fetch_status = 0 begin'
    
    --If unit level security is ON
    if @a_unit_level_security = 1
		--Checking the delete permission
		set @a_delete_trigger_sql = @a_delete_trigger_sql		
		      --The unit-level access
		+ '   if @a_has_full_rights = 0 and not exists(select 1 from meta_table_acl_unit_access s'
		+ '     inner join meta_user_units u on s.unit_id=u.unit_id'
		+ '     where table_id=0x' + @a_table_id_str 
		+ '       and s.user_name=lower(original_login()) and access_type=4 and u.user_name=@a_owner)'
		      --The own rows access
	    + '   and not exists(select 1 from meta_table_acl_own_rows where table_id=0x'
		+     @a_table_id_str + ' and @a_owner=lower(original_login())'
		+     ' and user_name=lower(original_login()) and access_type=4)'   
		+ '   begin'
		+ '     declare @a_row_id_str varchar(32);'
		+ '     set @a_row_id_str = dbo.p_bin_to_str(@a_row_id);'
		+ '     raiserror(''Insufficient privileges to delete the row (id = %s) (#error_code = 20004)'', 16, 1, @a_row_id_str);'
		+ '     return;'
		+ '   end;';
		
	--If recycle bin is enabled
    if @a_recycle_bin_option = 1
        --Performing the soft delete
        set @a_delete_trigger_sql = @a_delete_trigger_sql 
        + ' exec dbo.meta_soft_delete_table_row 0x' + @a_table_id_str + ', @a_row_id;';
    else
        --Performing the delete
        set @a_delete_trigger_sql = @a_delete_trigger_sql 
        + ' exec dbo.meta_delete_table_row 0x' + @a_table_id_str + ', @a_row_id;';
		
    set @a_delete_trigger_sql = @a_delete_trigger_sql
    + '   fetch next from @a_value_cursor into @a_row_id, @a_owner;'
    + ' end;'
    + ' close @a_value_cursor;'
    + ' deallocate @a_value_cursor;'
	----------------------------------------
    + ' end;';
    
    exec (@a_delete_trigger_sql);
    
    
    --Trigger for update
    declare @a_update_trigger_sql varchar(max);
    
    set @a_update_trigger_sql = 'create trigger j'
    + @a_table_id_str
    + ' on ' + @table_name
    + ' with execute as owner'
    + ' instead of update as begin'
    + ' set nocount on;'
    
    --If unit level security is ON
    if @a_unit_level_security = 1
		set @a_update_trigger_sql = @a_update_trigger_sql
		-- Checking update permission
		+ ' declare @a_row_id binary(16),'
		+ ' @a_old_owner varchar(26),'
		+ ' @a_has_full_rights numeric,'
		+ ' @a_new_value_cursor cursor, @a_row_count numeric;'
		    --Checking the full access rights
		+ ' select @a_has_full_rights = count(*) from meta_table_acl_full_access where table_id=0x'
		+     @a_table_id_str + ' and user_name=lower(original_login()) and access_type=3;'
		+ ' set @a_new_value_cursor = cursor fast_forward read_only for select id from inserted;'
		+ ' open @a_new_value_cursor;'
		+ ' fetch next from @a_new_value_cursor into @a_row_id;'
		+ ' while @@fetch_status = 0'
		+ ' begin'
		+ '   select @a_old_owner = owner from deleted where id = @a_row_id;'
		      --The unit-level access
		+ '   if @a_has_full_rights = 0 and not exists(select 1 from meta_table_acl_unit_access s'
		+ '     inner join meta_user_units u on s.unit_id=u.unit_id'
		+ '     where table_id=0x' + @a_table_id_str 
		+ '       and s.user_name=lower(original_login()) and access_type=3 and u.user_name=@a_old_owner)'
		      --The own rows access
	    + '   and not exists(select 1 from meta_table_acl_own_rows where table_id=0x'
		+     @a_table_id_str + ' and @a_old_owner=lower(original_login())'
		+     ' and user_name=lower(original_login()) and access_type=3)' 
		+ '   begin'
		+ '     declare @a_row_id_str varchar(32);'
		+ '     set @a_row_id_str = dbo.p_bin_to_str(@a_row_id);'
		+ '     raiserror(''Insufficient privileges to update the row (id = %s) (#error_code = 20003)'', 16, 1, @a_row_id_str);'
		+ '     return;'
		+ '   end;'
		+ '   fetch next from @a_new_value_cursor into @a_row_id;'
		+ ' end;'
		+ ' close @a_new_value_cursor;'
		+ ' deallocate @a_new_value_cursor;';
	
	set @a_update_trigger_sql = @a_update_trigger_sql
    --Performing the update----------------------------------------
    + ' update tbl_' + @table_name
    + ' set tbl_' + @table_name + '.owner = lower(inserted.owner),'
    + ' tbl_' + @table_name + '.updated_on = getdate(),'
    + ' tbl_' + @table_name + '.updated_by = lower(original_login()),'
    + ' tbl_' + @table_name + '.row_version = convert(binary(16), newid())';

	--Reading all custom columns
	declare @a_column_name varchar(26),
	@a_column_cursor cursor;
    
	set @a_column_cursor = cursor fast_forward read_only for
	select column_name from meta_table_columns
	where table_id = @a_table_id
	and customization_level > 0;
    
	open @a_column_cursor;
	fetch next from @a_column_cursor into @a_column_name;
    
	while @@fetch_status = 0
	begin
		set @a_update_trigger_sql = @a_update_trigger_sql +
		', tbl_' + @table_name + '.' + @a_column_name + '=inserted.' + @a_column_name;
		fetch next from @a_column_cursor into @a_column_name;
	end;
    
	close @a_column_cursor;
	deallocate @a_column_cursor;
    
    set @a_update_trigger_sql = @a_update_trigger_sql 
	+ ' from tbl_' + @table_name
	+ ' inner join inserted on tbl_' + @table_name +'.id = inserted.id;'
	+ ' end;';
    
    exec (@a_update_trigger_sql);
    
    --Setting table view grants
    exec dbo.p_set_table_view_grants @a_table_id;
end;


go

exec dbo.p_drop_proc_if_exists 'p_set_table_view_grants';

go

--Function of setting table view grants
create procedure p_set_table_view_grants(
    --Table id
    @a_table_id binary(16))
as
begin
    declare
    --Table name
    @a_table_name varchar(26);
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);

    --Reading table acl
    declare @access_type numeric(1),
    @principal_name varchar(26),
    @acl_cursor cursor;
    set @acl_cursor = cursor fast_forward read_only for
    select access_type, principal_name from meta_table_acl
    where table_id = @a_table_id;
    
    open @acl_cursor;
    fetch next from @acl_cursor into @access_type, @principal_name; 
    
    while @@fetch_status = 0
    begin        
        if @access_type = 1
            exec ('grant select on ' + @a_table_name + ' to ' + @principal_name);
        else if @access_type = 2
            exec ('grant insert on ' + @a_table_name + ' to ' + @principal_name);
        else if @access_type = 3
            exec ('grant update on ' + @a_table_name + ' to ' + @principal_name);
        else if @access_type = 4
            exec ('grant delete on ' + @a_table_name + ' to ' + @principal_name);
        
        fetch next from @acl_cursor into @access_type, @principal_name; 
    end;
    
    close @acl_cursor;
    deallocate @acl_cursor;
end;

go

exec dbo.p_drop_proc_if_exists 'p_check_table_not_exists';

go

--Function of checking that table doesn't exist
create procedure p_check_table_not_exists(
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1) output)
as
begin
    declare
    --Table id str
    @a_table_id_str varchar(32);
    
    --Searching for the table id
    if exists(select 1 from meta_tables
        where table_id = @a_table_id)
    begin
        set @a_table_id_str = dbo.p_bin_to_str(@a_table_id);
        raiserror('A table with the specified id was already added (table_id = %s) (#error_code = 20025)',
        16, 1, @a_table_id_str);
        set @a_result = 0;
        return;
    end;
    
    --Searching for the table name
    if exists(select 1 from meta_tables
        where table_name = @a_table_name)
    begin
        raiserror('A table with the specified name was already added (table_name = %s) (#error_code = 20026)',
        16, 1, @a_table_name);
        set @a_result = 0;
        return;
    end;
    
    set @a_result = 1;
end;

go

exec dbo.p_drop_proc_if_exists 'p_check_table_exists';

go

--Function of checking table existence
create procedure p_check_table_exists(
    --Table id
    @a_table_id binary(16),
    --Result
    @a_result numeric(1) output)
as  
begin
    --Searching for the table id
    if not exists(select 1from meta_tables
        where table_id = @a_table_id)
    begin
        declare
        --Table id str
        @a_table_id_str varchar(32);
        set @a_table_id_str = dbo.p_bin_to_str(@a_table_id);

        raiserror('Specified table was not found (table_id = %s) (#error_code = 20027)',
        16, 1, @a_table_id_str);
        set @a_result = 0;
        return;
    end;
    
    set @a_result = 1;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_add_table';

go

--Function of creating new user table
create procedure meta_add_table(
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(max),
    --Id column id
    @a_id_column_id binary(16),
    --Owner column id
    @a_owner_column_id binary(16),
    --CreatedBy column id
    @a_created_by_column_id binary(16),
    --CreatedOn column id
    @a_created_on_column_id binary(16),
    --UpdatedBy column id
    @a_updated_by_column_id binary(16),
    --UpdatedOn column id
    @a_updated_on_column_id binary(16),
    --DeletedBy column id
    @a_deleted_by_column_id binary(16),
    --DeletedOn column id
    @a_deleted_on_column_id binary(16),
    --IsActive column_id
    @a_is_active_column_id binary(16),
    --RowVersion column id
    @a_row_version_column_id binary(16))
with execute as owner
as
begin
	set nocount on;
	declare @a_result numeric(1);

    exec dbo.p_check_table_not_exists @a_table_id, @a_table_name, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;    
    begin tran;

    --Inserting metadata
    insert into meta_tables(table_id, table_name)
    values (@a_table_id, @a_table_name);

    --Creating user table
    exec ('create table tbl_'
    + @a_table_name
    + '(id binary(16), '
    + 'owner varchar(26) not null, '
    + 'is_active numeric(1) default 1 not null, '
    + 'created_by varchar(26) not null, '
    + 'created_on datetime not null, '
    + 'updated_by varchar(26) not null, '
    + 'updated_on datetime not null, '
    + 'row_version binary(16) not null,'
    + 'deleted_by varchar(26) null, '
    + 'deleted_on datetime null, '
    + 'check(len(rtrim(ltrim(owner)))>0),'
    + 'check(is_active=0 or is_active=1))');
    
    --Inserting system columns
    insert into meta_guid_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, description)
    values(@a_id_column_id, @a_table_id, 'id', 1, N'Id', 0, 0, N'The row id.');
    
    insert into meta_string_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, max_length, description)
    values(@a_owner_column_id, @a_table_id, 'owner', 1, N'Owner', 1, 0, 26, N'The row owner.');
    
    insert into meta_string_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, max_length, description)
    values(@a_created_by_column_id, @a_table_id, 'created_by', 1, N'Created by', 2, 0, 26, N'The name of the user that created the row.');
    
    insert into meta_datetime_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, description)
    values(@a_created_on_column_id, @a_table_id, 'created_on', 1, N'Created on', 3, 0, N'The date and time of the row creation.');
    
    insert into meta_string_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, max_length, description)
    values(@a_updated_by_column_id, @a_table_id, 'updated_by', 1, N'Updated by', 4, 0, 26, N'The name of the user that updated the row last.');
    
    insert into meta_datetime_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, description)
    values(@a_updated_on_column_id, @a_table_id, 'updated_on', 1, N'Updated on', 5, 0, N'The date and time of the last row update.');
    
    insert into meta_string_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, max_length, description)
    values(@a_deleted_by_column_id, @a_table_id, 'deleted_by', 0, N'Deleted by', 6, 0, 26, N'The name of the user that deleted the row.');
    
    insert into meta_datetime_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, description)
    values(@a_deleted_on_column_id, @a_table_id, 'deleted_on', 0, N'Deleted on', 7, 0, N'The date and time of the row deletion.');
    
    insert into meta_bool_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, description)
    values(@a_is_active_column_id, @a_table_id, 'is_active', 1, N'Is active', 8, 0, N'True if the row was not soft deleted.');
    
    insert into meta_guid_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, description)
    values(@a_row_version_column_id, @a_table_id, 'row_version', 1, N'Row version', 9, 0, N'The version of the row.');
        
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;

    --Creating table triggers
    exec dbo.p_create_table_triggers @a_table_id;
    
    --Granting access to schema administrators
    declare @acl_user_name varchar(26),
    @acl_cursor cursor;
    set @acl_cursor = cursor fast_forward read_only for
    select user_name from meta_users
    where can_change_schema = 1;
    
    open @acl_cursor;
    fetch next from @acl_cursor into @acl_user_name;
    
    while @@fetch_status = 0
	begin
        exec ('grant select, insert, update, delete on tbl_' 
        + @a_table_name + ' to ' + @acl_user_name);
        
        fetch next from @acl_cursor into @acl_user_name;
    end;
    
    close @acl_cursor;
    deallocate @acl_cursor;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_table';

go

--Function of deleting user table
create procedure meta_delete_table(
    --Table id
    @a_table_id binary(16))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table name
    @a_table_name varchar(30),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_table_exists @a_table_id, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;

    set @a_table_name = dbo.p_get_table_name(@a_table_id);

    --Deleting the metadata
    delete from meta_tables
    where table_id = @a_table_id;

    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;

    --Dropping the table
    set @a_table_name = 'tbl_' + @a_table_name;
    exec dbo.p_drop_table_if_exists @a_table_name;
   
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_rename_table';

go

--Function of table renaming
create procedure meta_rename_table(
    --Table id
    @a_table_id binary(16),
    --New table name
    @a_new_table_name varchar(max))
with execute as owner
as
begin
	declare
	--Old table name
    @a_old_table_name varchar(30),
    --Result
    @a_result numeric(1);
    
    set nocount on;
    
    exec dbo.p_check_table_exists @a_table_id, @a_result output;
    
    if @a_result = 0 return;
    
    --Searching for a table with the same name
    if exists(select 1 from meta_tables
		where table_name = @a_new_table_name)
    begin
        raiserror('A table with the specified name was already added (table_name = %s) (#error_code = 20026)',
        16, 1, @a_new_table_name);
        return;
    end;
    
    set @a_old_table_name = dbo.p_get_table_name(@a_table_id);

    --If there is no need for renaming
    if @a_old_table_name = @a_new_table_name
        return;
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    update meta_tables
    set table_name = @a_new_table_name
    where table_id = @a_table_id;    
    
    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_old_table_name;
    
    --Dropping table triggers
    exec dbo.p_drop_table_triggers @a_table_id;
    
    --Renaming the table
    set @a_old_table_name = 'tbl_' + @a_old_table_name;
    declare @a_new_table_name_modified varchar(30);
    set @a_new_table_name_modified = 'tbl_' + @a_new_table_name;
    exec sp_rename @a_old_table_name, @a_new_table_name_modified, 'OBJECT';
    
    --Creating table triggers
    exec dbo.p_create_table_triggers @a_table_id;
    
    --Recreating index triggers for all table columns
    declare @a_column_id binary(16),
    @col_cursor cursor;
    set @col_cursor = cursor fast_forward read_only for
    select column_id from meta_table_columns
    where table_id = @a_table_id;
    
    open @col_cursor;
    fetch next from @col_cursor into @a_column_id;
    
    while @@fetch_status = 0
    begin   
		--Recreating column index triggers
        exec dbo.p_recreate_col_index_triggers @a_column_id;
        
		fetch next from @col_cursor into @a_column_id;
    end;
    
    close @col_cursor;
    deallocate @col_cursor; 

    
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_recycle_bin';

go

--Function of setting the availability of the recycle bin
create procedure meta_set_recycle_bin(
    --The availability of the recycle bin
    @a_recycle_bin_enabled numeric(10))
with execute as owner
as
begin
    declare
    --Table name
    @a_table_name varchar(26),
    --Table id
    @a_table_id binary(16);
    
    set nocount on;
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    update meta_options
    set int_value = @a_recycle_bin_enabled
    where option_name = 'RECYCLE_BIN';
    
    declare @a_table_cursor cursor;
    set @a_table_cursor = cursor fast_forward read_only for
    select table_id, table_name from meta_tables;
    
    open @a_table_cursor;
    fetch next from @a_table_cursor into @a_table_id, @a_table_name;

    --Recreating table views
    while @@fetch_status = 0
    begin
        exec dbo.p_drop_view_if_exists @a_table_name;
        exec dbo.p_create_table_view @a_table_id;
        
        fetch next from @a_table_cursor into @a_table_id, @a_table_name;
    end;
    
    close @a_table_cursor;
    deallocate @a_table_cursor;
    
    commit;    
end;

go

--2-----------------------------------------------------------
go

exec dbo.p_drop_func_if_exists 'p_generate_order_index';

go

--Function that generates new column order index
create function p_generate_order_index(
    --Table id
    @a_table_id binary(16))
    returns numeric(5)
as
begin
	declare @a_order_index numeric(5);
	
    select @a_order_index = max(order_index) + 1
    from meta_table_columns
    where table_id = @a_table_id;
    
	if @a_order_index is null
		set @a_order_index = 0;
    
    return @a_order_index;
end;

go

exec dbo.p_drop_proc_if_exists 'p_check_col_not_exists';

go

--Function of checking that column with the specified id and name doesn't exist
create procedure p_check_col_not_exists(
    --Table id
    @a_table_id binary(16),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(26),
    --Result
    @a_result numeric(1) output)
as
begin
	declare
	--Column id str
	@a_column_id_str varchar(32),
	--Row count
    @a_row_count numeric;    
    
    --Searching for the column id
    select @a_row_count = count(*)
    from meta_table_columns
    where column_id = @a_column_id;
    
    if @a_row_count > 0
    begin
		set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
		
        raiserror('A column with the specified id was already added (column_id = %s) (#error_code = 20020)',
        16, 1, @a_column_id_str);
        set @a_result = 0;
        return;
    end;
    
    --Searching for the column name
    select @a_row_count = count(*)
    from meta_table_columns
    where table_id = @a_table_id and column_name = @a_column_name;
    
    if @a_row_count > 0
    begin
        raiserror('A column with the specified name was already added (column_name = %s) (#error_code = 20021)',
        16, 1, @a_column_name);
        set @a_result = 0;
        return;
    end;
    
    set @a_result = 1;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_add_string_column';

go

--Function of adding string column
create procedure meta_add_string_column(
    --Table id
    @a_table_id binary(16),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(max))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_not_exists @a_table_id,
    @a_column_id, @a_column_name, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    set nocount on;
    begin tran;
    
    set @a_table_name =  dbo.p_get_table_name(@a_table_id);
    
    --Changing the metadata
    insert into meta_string_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values(@a_column_id, @a_table_id, @a_column_name,
        dbo.p_generate_order_index(@a_table_id), 1);

    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;

    exec ('alter table tbl_' 
    + @a_table_name
    + ' add ' + @a_column_name
    + ' nvarchar(2000)');
        
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
    
    commit;   
end;

go

exec dbo.p_drop_proc_if_exists 'p_check_col_exists';

go

--Function of checking column existence
create procedure p_check_col_exists(
    --Column id
    @a_column_id binary(16),
    --Result
    @a_result numeric(1) output)
as
begin
	declare
	--Column id str
	@a_column_id_str varchar(32),
	--Row count
    @a_row_count numeric;
    
    --Searching for the column
    select @a_row_count = count(*)
    from meta_table_columns
    where column_id = @a_column_id;
    
    if @a_row_count = 0
    begin
		set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
        raiserror('Specified column was not found (column_id = %s) (#error_code = 20019)',
        16, 1, @a_column_id_str);
        set @a_result = 0;
        return;
    end;
    
    set @a_result = 1;
end;

go

exec dbo.p_drop_proc_if_exists 'p_check_col_type';

go

--Function of checking column type
create procedure p_check_col_type(
    --Column id
    @a_column_id binary(16),
    --Column table name
    @a_column_table_name varchar(26),
    --Column type name
    @a_column_type_name varchar(26),
    --Result
    @a_result numeric(1) output)
as
begin
	declare
	--Column id str
	@a_column_id_str varchar(32),
	--Row count
    @a_row_count numeric;
    
	declare @check_col_existence_sql nvarchar(500);
	set @check_col_existence_sql = 'select @row_count_out = count(*) from ' + @a_column_table_name
		+ ' where column_id = @col_id';
    
    exec sp_executesql @check_col_existence_sql,
		N'@col_id binary(16), @row_count_out int output', 
		@col_id = @a_column_id,
		@row_count_out = @a_row_count output;
    
    if @a_row_count = 0
    begin
		set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
		declare @a_err_message varchar(500);
		set @a_err_message = 'Specified column (of type '
                + @a_column_type_name
                + ') was not found (column_id = %s) (#error_code = 20019)';
                
        raiserror(@a_err_message, 16, 1, @a_column_id_str);
        
        set @a_result = 0;
        return;
    end;
    
    set @a_result = 1;
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_col_constraints';

go

--Function of dropping column constraints
create procedure p_drop_col_constraints(
	--Column id
	@a_column_id binary(16))
as
begin
	declare
	--Constraint cursor
	@a_constraint_cursor cursor,
	--Column name
	@a_column_name varchar(26),
	--Constraint name
	@a_constraint_name varchar(255),
	--Table id
	@a_table_id binary(16),
	--Table name
	@a_table_name varchar(26);
	
	select @a_column_name = column_name,
	@a_table_id = table_id
	from meta_table_columns
	where column_id = @a_column_id;
	
	set @a_table_name = dbo.p_get_table_name(@a_table_id);
	
	--Dropping usual constraints
	set @a_constraint_cursor = cursor fast_forward read_only for
	select constraint_name
	from information_schema.constraint_column_usage
	where column_name = @a_column_name
	and table_name = 'tbl_' + @a_table_name;
	
	open @a_constraint_cursor;
	fetch next from @a_constraint_cursor into @a_constraint_name;
	
	while @@fetch_status = 0
	begin
		exec ('alter table tbl_' + @a_table_name
		+ ' drop constraint ' + @a_constraint_name);
		
		fetch next from @a_constraint_cursor into @a_constraint_name;
	end;
	
	close @a_constraint_cursor;
	deallocate @a_constraint_cursor;
	
	--Dropping default constraints
	set @a_constraint_name = null;
	
	select @a_constraint_name = name 
	from sys.default_constraints 
	where parent_object_id = object_id('tbl_' + @a_table_name)
	and type = 'D'
	and parent_column_id = (
		select column_id 
		from sys.columns 
		where object_id = object_id('tbl_' + @a_table_name)
		and name = @a_column_name);
		
	if @a_constraint_name is not null
		exec ('alter table tbl_' + @a_table_name
		+ ' drop constraint ' + @a_constraint_name);
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_column';

go

--Function of removing string column
create procedure meta_delete_column(
    --Column id
    @a_column_id binary(16))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table id
    @a_table_id binary(16),
    --Column name
    @a_column_name varchar(26),
    --Table name
    @a_table_name varchar(26),
    --Table column name
    @a_column_table_name varchar(30),
    --Customization level
    @a_customization_level numeric(10),
    --Column id str
    @a_column_id_str varchar(32),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_exists @a_column_id, @a_result output;
    
    if @a_result = 0 return;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);

    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_table_name = table_name,
    @a_column_table_name = column_type, @a_customization_level = customization_level
    from meta_table_columns
    where column_id = @a_column_id;
    
    --If this is a system column
    if @a_customization_level = 0
    begin
        raiserror('Deleting system column is forbidden (column_id = %s) (#error_code = 20005)',
        16, 1, @a_column_id_str);
        return;
    end;
    
    set xact_abort on;
    begin tran;
    
    --Deleting column constraints(if any)
    exec dbo.p_drop_col_constraints @a_column_id;
    
    --Deleting column indexes (if any)
    exec dbo.p_delete_column_indexes @a_column_id;
    
    --If this is ref column
    if @a_column_table_name like '%meta_ref_columns'
        --Dropping ref checking trigger
        exec dbo.p_drop_ref_checking @a_column_id;
    
    --Updating the metadata
    exec ('delete from ' + @a_column_table_name
    + ' where column_id = 0x' + @a_column_id_str);
    
    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
    
    exec ('alter table tbl_'
    + @a_table_name
    + ' drop column ' + @a_column_name);
    
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_rename_column';

go

--Function of renaming string column
create procedure meta_rename_column(
    --Column id
    @a_column_id binary(16),
    --New column name
    @a_new_column_name varchar(max))
with execute as owner
as  
begin
	set nocount on;
	declare
	--Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Old column name
    @a_old_column_name varchar(26),
    --Column renaming feature
    @column_renaming_is_enabled numeric(1),
    --Column table name
    @a_column_table_name varchar(30),
    --Customization level
    @a_customization_level numeric(10),
    --Row count
    @a_row_count numeric,
    --Column id str
    @a_column_id_str varchar(32),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_exists @a_column_id, @a_result output;
    
    if @a_result = 0 return;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_id = table_id, @a_old_column_name = column_name,
    @a_table_name = table_name,
    @a_column_table_name = column_type, @a_customization_level = customization_level
    from meta_table_columns
    where column_id = @a_column_id;
    
    --Searching for the column with duplicate name
    select @a_row_count = count(*)
    from meta_table_columns
    where table_id = @a_table_id and column_name = @a_new_column_name;
    
    if @a_row_count > 0
    begin
        raiserror('A column with the specified name was already added (column_name = %s) (#error_code = 20021)',
        16, 1, @a_new_column_name);
        return;
    end;

    --If there is no need for renaming
    if @a_old_column_name = @a_new_column_name
        return;
    
    --If this is a system column
    if @a_customization_level = 0
    begin
        raiserror('Renaming system column is forbidden (column_id = %s) (#error_code = 20006)',
        16, 1, @a_column_id_str);
        return;
    end;
    
    --Checking column renaming
    select @column_renaming_is_enabled = is_enabled
    from meta_dbms_features
    where feature_name = 'COLUMN_RENAMING';
    
    --If feature is enabled
    if @column_renaming_is_enabled = 1
    begin
		set xact_abort on;
		begin tran;
      
        --Changing the metadata
        exec ('update ' + @a_column_table_name
        + ' set column_name = ''' + @a_new_column_name + ''''
        + ' where column_id = 0x' + @a_column_id_str);
        
        --Dropping table view
        exec dbo.p_drop_view_if_exists @a_table_name;
        
        --Droppping ALL column constraints (otherwise - it's impossible to rename)
        --If this is ref column
        if @a_column_table_name like '%meta_ref_columns'
            exec dbo.p_drop_ref_col_constrs @a_column_id;
		--If this is enum column
		else if @a_column_table_name like '%meta_enum_columns'
			exec dbo.p_drop_enum_col_constrs @a_column_id;
		--If this decimal column
		else if @a_column_table_name like '%meta_decimal_columns'
			exec dbo.p_drop_decimal_col_constrs @a_column_id;
        --If this is bool column
        else if @a_column_table_name like '%meta_bool_columns'
			exec dbo.p_drop_bool_col_constrs @a_column_id;
		--If this is int column
		else if @a_column_table_name like '%meta_int_columns'
			exec dbo.p_drop_int_col_constrs @a_column_id;
		--If this is small int column
		else if @a_column_table_name like '%meta_small_int_columns'
			exec dbo.p_drop_small_int_col_constrs @a_column_id;
		--If this is big int column
		else if @a_column_table_name like '%meta_big_int_columns'
			exec dbo.p_drop_big_int_col_constrs @a_column_id;
		--If this is datetime column
		else if @a_column_table_name like '%meta_datetime_columns'
			exec dbo.p_drop_datetime_col_constrs @a_column_id;
        
        --Object name
        declare @a_obj_name varchar(64);
        set @a_obj_name = 'tbl_' + @a_table_name + '.' + @a_old_column_name;
        
        exec sp_rename 
		@objname= @a_obj_name, 
		@newname = @a_new_column_name, 
		@objtype = 'COLUMN';
        
        --Restoring ALL column constraints
        --If this is ref column
        if @a_column_table_name like '%meta_ref_columns'
            --Creating ref checking trigger
            exec dbo.p_create_ref_col_constrs @a_column_id;
		--If this is enum column
		else if @a_column_table_name like '%meta_enum_columns'
			exec dbo.p_create_enum_col_constrs @a_column_id;
		--If this decimal column
		else if @a_column_table_name like '%meta_decimal_columns'
			exec dbo.p_create_decimal_col_constrs @a_column_id;
        --If this is bool column
        else if @a_column_table_name like '%meta_bool_columns'
			--Creating bool check
			exec dbo.p_create_bool_col_constrs @a_column_id;
		--If this is int column
		else if @a_column_table_name like '%meta_int_columns'
			exec dbo.p_create_int_col_constrs @a_column_id;
		--If this is small int column
		else if @a_column_table_name like '%meta_small_int_columns'
			exec dbo.p_create_small_int_col_constrs @a_column_id;
		--If this is big int column
		else if @a_column_table_name like '%meta_big_int_columns'
			exec dbo.p_create_big_int_col_constrs @a_column_id;
		--If this is datetime column
		else if @a_column_table_name like '%meta_datetime_columns'
			exec dbo.p_create_datetime_col_constrs @a_column_id;
			
		--Recreating column index triggers
        exec dbo.p_recreate_col_index_triggers @a_column_id;
        
        --Creating table view
        exec dbo.p_create_table_view @a_table_id;
        
        commit;
    end;        
    else
	begin
        raiserror('Renaming a column is not supported by the dbms (column_id = %s) (#error_code = 20007)',
        16, 1, @a_column_id_str);
		return;
    end;
end;

go

exec dbo.p_drop_func_if_exists 'p_get_col_type';

go

--Function of getting column type
create function p_get_col_type(
	--Column id
	@a_column_id binary(16))
returns varchar(64)
as
begin
	declare
	--Column type str
	@a_col_type_str varchar(64),
	--Table id
	@a_table_id binary(16),
	--Table name
	@a_table_name varchar(26),
	--Column name
	@a_column_name varchar(26),
	--Data type
	@a_data_type varchar(64),
	--Character maximum length
	@a_char_max_length varchar(64),
	--Numeric precision
	@a_num_precision varchar(5),
	--Numeric scale
	@a_num_scale varchar(5);	
	
	--Reading column properties
	select @a_table_id = table_id,
	@a_column_name = column_name,
	@a_table_name = table_name
	from meta_table_columns
	where column_id = @a_column_id;
	
	--Reading type properties
	select @a_data_type = data_type,
	@a_char_max_length = character_maximum_length,
	@a_num_precision = numeric_precision,
	@a_num_scale = numeric_scale
	from information_schema.columns
	where table_name = 'tbl_' + @a_table_name
	and column_name = @a_column_name;
	
	set @a_col_type_str = @a_data_type;
	
	-- If the maximum string length is specified
	if (@a_char_max_length is not null) and (@a_char_max_length <> '-1')
		set @a_col_type_str = @a_col_type_str + '(' + @a_char_max_length + ')';
	-- If it is a binary max column
	else if (@a_col_type_str = 'varbinary' or @a_col_type_str = 'nvarchar')
	 and @a_char_max_length = '-1'
		set @a_col_type_str = @a_col_type_str + '(max)';
	-- If the decimal precision and scale are specified
	else if @a_num_precision is not null and @a_num_scale is not null
		set @a_col_type_str = @a_col_type_str + '(' + @a_num_precision + ','
		+ @a_num_scale + ')';
	
	return @a_col_type_str;
end;

go

exec dbo.p_drop_proc_if_exists 'p_recreate_column_indexes';

go

--Function that recreates column indexes
create procedure p_recreate_column_indexes(
	--Column id
    @a_column_id binary(16))
as
begin
	declare
	--Table id
    @a_table_id binary(16);
    
    --Reading column table name
    select @a_table_id = table_id
    from meta_table_columns
    where column_id = @a_column_id;
    
	--Recreating the column indexes
	declare @index_cursor cursor;
	set @index_cursor = cursor fast_forward read_only for
	select index_id, is_unique, column_id_list
	from ##column_indexes;
    
    declare @a_index_id binary(16),
			@a_is_unique numeric(1),
			@a_column_id_list varbinary(256);
			
	open @index_cursor;
	fetch next from @index_cursor into @a_index_id, @a_is_unique, @a_column_id_list;
	
	while @@fetch_status = 0
	begin
		exec dbo.meta_add_index @a_index_id, @a_table_id, @a_is_unique, @a_column_id_list;
		
		fetch next from @index_cursor into @a_index_id, @a_is_unique, @a_column_id_list;
	end;
	
	close @index_cursor;
	deallocate @index_cursor;
	
	delete from ##column_indexes;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_column_nullability';

go

--Function of setting column nullability
create procedure meta_set_column_nullability(
    --Column id
    @a_column_id binary(16),
    --Is not null
    @a_is_not_null numeric(1))
with execute as owner
as  
begin
	set nocount on;
	declare
	--Table id
    @a_table_id binary(16),
    --Backend table name
    @a_column_table_name varchar(30),
    --Column name
    @a_column_name varchar(26),
    --Old is_not_null
    @a_old_is_not_null numeric(1),
    --Table name
    @a_table_name varchar(26),
    --Customization level
    @a_customization_level numeric(10),
    --Column id str
    @a_column_id_str varchar(32),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_exists @a_column_id, @a_result output;
    
    if @a_result = 0 return;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column table name
    select @a_column_table_name = column_type,
    @a_customization_level = customization_level
    from meta_table_columns
    where column_id = @a_column_id;
    
    --If this is a system column
    if @a_customization_level = 0
    begin
        raiserror('Changing system column nullability is forbidden (column_id = %s) (#error_code = 20008)',
        16, 1, @a_column_id_str);
        return;
    end;
    
    --Reading column properties
    declare @read_col_props_sql nvarchar(500);
	set @read_col_props_sql	= 'select @a_table_id_out = table_id, @a_is_not_null_out = is_not_null,'
		+ ' @a_column_name_out = column_name from ' + @a_column_table_name
		+ ' where column_id = @col_id';
    
    exec sp_executesql @read_col_props_sql,
		N'@col_id binary(16), @a_table_id_out binary(16) output, @a_is_not_null_out numeric(1) output, @a_column_name_out varchar(26) output', 
		@col_id = @a_column_id,
		@a_table_id_out = @a_table_id output,
		@a_is_not_null_out = @a_old_is_not_null output,
		@a_column_name_out = @a_column_name output;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    if @a_is_not_null = @a_old_is_not_null
        return;
    
    set xact_abort on;
    begin tran;
    
    --Dropping all column indexes (otherwise SQL Server refuses to alter the column)
    exec dbo.p_delete_column_indexes @a_column_id, /*Saving the index list*/ 1;
    
    declare @a_column_type_str varchar(64);
    set @a_column_type_str = dbo.p_get_col_type(@a_column_id);
    
    if @a_is_not_null = 1
        exec ('alter table tbl_' 
        + @a_table_name
        + ' alter column ' + @a_column_name
        + ' ' +@a_column_type_str
        + ' not null');
    else
        exec ('alter table tbl_' 
        + @a_table_name
        + ' alter column ' + @a_column_name
        + ' ' + @a_column_type_str
        + ' null');
    
    --Changing the metadata
    exec ('update ' + @a_column_table_name
    + ' set is_not_null = ' + @a_is_not_null
    + ' where column_id = 0x' + @a_column_id_str);
    
    --Recreating the column indexes
	exec dbo.p_recreate_column_indexes @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_string_default';

go

--Function of setting column default value
create procedure meta_set_string_default(
    --Column id
    @a_column_id binary(16),
    --Default value
    @a_default_value nvarchar(2000))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Customization level
    @a_customization_level numeric(10),
    --Column id str
    @a_column_id_str varchar(32),
    --Result
    @a_result numeric(1),
    --Old default value
    @a_old_default_value nvarchar(2000);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_string_columns', 'string', @a_result output;
    
    if @a_result = 0 return;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_customization_level = customization_level,
    @a_old_default_value = default_value
    from meta_string_columns
    where column_id = @a_column_id;
    
    --If this is a system column
    if @a_customization_level = 0
    begin
         raiserror('Changing string system column default value is forbidden (column_id = %s) (#error_code = 20008)',
         16, 1, @a_column_id_str);
         return;
    end;
    
    --If there is no need for change
    if (@a_old_default_value = @a_default_value) or
    (@a_old_default_value is null and @a_default_value is null)
		return;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    set xact_abort on;
    begin tran;
    
    --If there is an old default value
    if @a_old_default_value is not null
		--Dropping the old default value
		exec ('alter table tbl_' 
        + @a_table_name
        + ' drop constraint def_' + @a_column_id_str);
    
    --Updating the metadata
    update meta_string_columns
    set default_value = @a_default_value
    where column_id = @a_column_id;
    
    --If there is a new default value
    if @a_default_value is not null
        exec ('alter table tbl_' 
        + @a_table_name
        + ' add constraint def_' + @a_column_id_str
        + ' default ''' + @a_default_value + ''' for ' + @a_column_name);        
        
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_string_max_length';

go

--Function of setting string column max length
create procedure meta_set_string_max_length(
    --Column id
    @a_column_id binary(16),
    --Max length
    @a_max_length integer)
with execute as owner
as
begin
	set nocount on;
	declare
	--Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Customization level
    @a_customization_level numeric(10),
    --Column id str
    @a_column_id_str varchar(32),
    --Is not null
    @a_is_not_null numeric(1),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_string_columns', 'string', @a_result output;
    
    if @a_result = 0 return;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_customization_level = customization_level, @a_is_not_null = is_not_null
    from meta_string_columns
    where column_id = @a_column_id;
    
    --If this is a system column
    if @a_customization_level = 0
    begin
        raiserror('Changing string system column max length is forbidden (column_id = %s) (#error_code = 20008)',
        16, 1, @a_column_id_str);
        return;
    end;

    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    set xact_abort on;
    begin tran;
    
    --Dropping all column indexes (otherwise SQL Server refuses to alter the column)
    exec dbo.p_delete_column_indexes @a_column_id, /*Saving the index list*/ 1;

	declare @a_sql_str varchar(500);

    if @a_max_length is not null
        set @a_sql_str = 'alter table tbl_' 
        + @a_table_name
        + ' alter column ' + @a_column_name
        + ' nvarchar(' + cast(@a_max_length as varchar) + ')';
    else
        set @a_sql_str = 'alter table tbl_' 
        + @a_table_name
        + ' alter column ' + @a_column_name
        + ' nvarchar(2000)';
    
    if @a_is_not_null = 1
		set @a_sql_str = @a_sql_str + ' not null';
		
	exec (@a_sql_str);
	
	--Recreating the column indexes
	exec dbo.p_recreate_column_indexes @a_column_id;
        
    --Updating the metadata
    update meta_string_columns
    set max_length = @a_max_length
    where column_id = @a_column_id;
    
    commit;
end;

--3-----------------------------------------------------------------------

go

--Function of checking reference value
exec dbo.p_drop_proc_if_exists 'p_check_reference_value';

go

--Function of checking reference value
create procedure p_check_reference_value(
    --Column id
    @a_column_id binary(16),
    --Reference value
    @a_ref_value binary(16))
with execute as owner
as
begin
	declare
	--Ref table name
    @a_ref_table_name varchar(26),
    --Row count
    @a_row_count numeric;
     
	--If value is null there is no need for any check
	if @a_ref_value is null
		return;
		
	declare
	--Ref table cursor
	@a_ref_cursor cursor,
	--Ref table id
	@a_ref_table_id binary(16);
	
	set @a_ref_cursor = cursor fast_forward read_only for
	select ref_table_id from meta_ref_targets
	where column_id = @a_column_id;
	
	open @a_ref_cursor;
	fetch next from @a_ref_cursor into @a_ref_table_id;

	--Enumerating all tables that the column references 
	while @@fetch_status = 0
	begin 
		--Reading ref table name
		select @a_ref_table_name = table_name
		from meta_tables
		where table_id = @a_ref_table_id;
	 
		--Searching for referenced row
		declare @ref_search_sql nvarchar(500);
		set @ref_search_sql = 'select @row_count_out = count(*) from tbl_' + @a_ref_table_name
		+ ' where id = @row_id';

		exec sp_executesql @ref_search_sql,
		N'@row_id binary(16), @row_count_out int output', 
		@row_id = @a_ref_value,
		@row_count_out = @a_row_count output;
	 
		--If referenced row was found
		if @a_row_count > 0
			return;
			
		fetch next from @a_ref_cursor into @a_ref_table_id;       
	end;
	
	close @a_ref_cursor;
	deallocate @a_ref_cursor;
	
	declare
	--Ref value str
    @a_ref_value_str varchar(32),
    --Column id str
    @a_column_id_str varchar(32);
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    set @a_ref_value_str = dbo.p_bin_to_str(@a_ref_value);

	--Referenced row was not found
	raiserror('A row that was referenced by the column was not found (column_id = %s, ref_value = %s) (#error_code = 20024)',
			16, 1, @a_column_id_str, @a_ref_value_str);
	rollback;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_ref_checking';

go

--Function of creating check ref value trigger
create procedure p_create_ref_checking(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Table id
    @a_table_id binary(16),
    --Column name
    @a_column_name varchar(26),
    --Table name
    @a_table_name varchar(26),
    --Trigger name
    @a_trigger_name varchar(33),
    --Column id str
    @a_column_id_str varchar(32);
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_column_name = column_name, @a_table_id = table_id
    from meta_ref_columns
    where column_id = @a_column_id;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
 
    --Creating trigger of checking column value
    set @a_trigger_name = 'r' + dbo.p_bin_to_str(@a_column_id);
    
    exec ('create trigger ' + @a_trigger_name
    + ' on tbl_' + @a_table_name
    + ' after insert, update as'
    + ' begin'
    + ' declare @ref_value binary(16);'
    + ' declare @ref_cursor cursor;'
    + ' set @ref_cursor = cursor fast_forward read_only for select '
    + @a_column_name + ' from inserted;'
    + ' open @ref_cursor;'
    + ' fetch next from @ref_cursor into @ref_value;'
    + ' while @@fetch_status = 0'
    + ' begin'
    + ' exec dbo.p_check_reference_value 0x'
    + @a_column_id_str + ', @ref_value;'
    + ' fetch next from @ref_cursor into @ref_value;'
    + ' end;'
    + ' close @ref_cursor;'
    + ' deallocate @ref_cursor;'
    + ' end;');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_ref_checking';

go

--Function of dropping check ref value trigger
create procedure p_drop_ref_checking(
    --Column id
    @a_column_id binary(16))
as
begin
    --Column id str
    declare @a_column_id_str varchar(32);
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    exec ('drop trigger ' + 'r' + @a_column_id_str);
end;

go

exec dbo.p_drop_proc_if_exists 'meta_add_ref_column';

go

--Function of adding reference column
create procedure meta_add_ref_column(
    --Table id
    @a_table_id binary(16),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(max))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);    
    
    exec dbo.p_check_col_not_exists @a_table_id, @a_column_id, @a_column_name, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    set @a_table_name =  dbo.p_get_table_name(@a_table_id);
     
    --Changing the metadata
    insert into meta_ref_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (@a_column_id, @a_table_id, @a_column_name,
        dbo.p_generate_order_index(@a_table_id), 1);
        
    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
         
    --Creating new column
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add ' + @a_column_name
    + ' binary(16)');
         
    --Creating trigger of checking column value
    exec dbo.p_create_ref_checking @a_column_id;
         
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_ref_default';

go

--Function of setting column default value
create procedure meta_set_ref_default(
    --Column id
    @a_column_id binary(16),
    --Default value
    @a_default_value binary(16))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32),
    --Default value str
    @a_default_value_str varchar(32),
    --Result
    @a_result numeric(1),
    --Old default value
    @a_old_default_value binary(16);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_ref_columns', 'reference', @a_result output;
    
    if @a_result = 0 return;
    
    if @a_default_value is not null
        set @a_default_value_str = dbo.p_bin_to_str(@a_default_value);
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_old_default_value = default_value
    from meta_ref_columns
    where column_id = @a_column_id;
    
    --If there is no need for change
    if (@a_old_default_value = @a_default_value) or
    (@a_old_default_value is null and @a_default_value is null)
        return;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    update meta_ref_columns
    set default_value = @a_default_value
    where column_id = @a_column_id;
    
     if @a_default_value is not null
        exec ('alter table tbl_' 
        + @a_table_name
        + ' add constraint def_' + @a_column_id_str
        + ' default 0x' + @a_default_value_str
        + ' for ' + @a_column_name);
    else
        exec ('alter table tbl_' 
        + @a_table_name
        + ' drop constraint def_' + @a_column_id_str);
        
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_add_ref_target';

go

--Function of adding ref target
create procedure meta_add_ref_target(
    --Column id
    @a_column_id binary(16),
    --Ref table id
    @a_ref_table_id binary(16))
with execute as owner
as
begin
    set nocount on;
    declare
    --Row count
    @a_row_count numeric,
    --Column id str
    @a_column_id_str varchar(32),
    --Ref table id str
    @a_ref_table_id_str varchar(32),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_ref_columns', 'reference', @a_result output;
    
    if @a_result = 0 return;
    
    select @a_row_count = count(*)
    from meta_ref_targets
    where column_id = @a_column_id and ref_table_id = @a_ref_table_id;
    
    if @a_row_count > 0
    begin
        set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
        set @a_ref_table_id_str = dbo.p_bin_to_str(@a_ref_table_id);
        
        raiserror('Adding duplicate reference target is forbidden (column_id = %s, ref_table_id = %s) (#error_code = 20022)',
        16, 1, @a_column_id_str, @a_ref_table_id_str);
        return;
    end;
    
    set xact_abort on;
    begin tran;
     
    insert into meta_ref_targets (column_id, ref_table_id)
    values (@a_column_id, @a_ref_table_id);
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_ref_target';

go

--Function of removing ref target
create procedure meta_delete_ref_target(
    --Column id
    @a_column_id binary(16),
    --Ref table id
    @a_ref_table_id binary(16))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Ref column name
    @a_ref_column_name varchar(26),
    --Ref table name
    @a_ref_table_name varchar(26),
    --Row count
    @a_row_count numeric;
    
    --Searching for the target
    select @a_row_count = count(*)
    from meta_ref_targets
    where column_id = @a_column_id
    and ref_table_id = @a_ref_table_id;
    
    if @a_row_count = 0
    begin
        declare @a_column_id_str varchar(32),
        @a_ref_table_id_str varchar(32);
        
        set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
        set @a_ref_table_id_str = dbo.p_bin_to_str(@a_ref_table_id);
        
        raiserror('Deleting nonexistent reference target is forbidden (column_id = %s, ref_table_id = %s) (#error_code = 20023)',
        16, 1, @a_column_id_str, @a_ref_table_id_str);
        return;
    end;

    --Reading ref column properties
    select @a_ref_column_name = column_name,
    @a_table_id = table_id
    from meta_ref_columns
    where column_id = @a_column_id;
    
    set xact_abort on;
    begin tran;
        
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    set @a_ref_table_name = dbo.p_get_table_name(@a_ref_table_id);    

    exec ('update tbl_'
    + @a_table_name
    + ' set ' + @a_ref_column_name + ' = null'
    + ' where exists('
    + 'select id from tbl_' + @a_ref_table_name
    + ' as ref where ref.id = tbl_' + @a_table_name + '.' + @a_ref_column_name + ')');

    delete from meta_ref_targets
    where column_id = @a_column_id
    and ref_table_id = @a_ref_table_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_ref_col_constrs';

go

--Function of creating all ref constraints
create procedure p_create_ref_col_constrs(
    --Column id
    @a_column_id binary(16))
as
begin
    --Creating trigger of checking column value
    exec dbo.p_create_ref_checking @a_column_id;
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_ref_col_constrs';

go

--Function of droppping all ref constraints
create procedure p_drop_ref_col_constrs(
    --Column id
    @a_column_id binary(16))
as
begin
    --Dropping trigger of checking column value
    exec dbo.p_drop_ref_checking @a_column_id;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_ref_checking';

go

-- Function of setting the need to check reference values
create procedure meta_set_ref_checking(
    -- Column id
    @a_column_id binary(16),
    -- The need to check reference values
    @a_ref_checking numeric(1))
with execute as owner
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric(1),
    --Table name
    @a_table_name varchar(26),
    --Trigger name
    @a_trigger_name varchar(33);

    exec dbo.p_check_col_type @a_column_id, 'meta_ref_columns', 'reference', @a_result output;
    
    if @a_result = 0 return;
    
    --The trigger name    
    set @a_trigger_name = 'r' + dbo.p_bin_to_str(@a_column_id);
    
    --The table name
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
   
    set xact_abort on;
    begin tran;
    
    if @a_ref_checking = 1
        exec ('enable trigger ' + @a_trigger_name + ' on tbl_' + @a_table_name);
    else
        exec ('disable trigger ' + @a_trigger_name + ' on tbl_' + @a_table_name);
    
    --Updating the metadata
    update meta_ref_columns
    set ref_checking = @a_ref_checking 
    where column_id = @a_column_id;
    
    commit;
end;

--4-----------------------------------------------------------------------
go

exec dbo.p_drop_proc_if_exists 'p_create_enum_constraint';

go

--Function of adding enum values constraint
create procedure p_create_enum_constraint(
       --Column id
       @a_column_id binary(16))
as
begin
    declare
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Enum value list
    @a_enum_value_list varchar(max),
    --Column id str
    @a_column_id_str varchar(32);
    
    set @a_enum_value_list = '';
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name
    from meta_enum_columns
    where column_id = @a_column_id;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Enum value cursor
    declare @a_value_cursor cursor,
    --Enum value
    @a_enum_value numeric;
    
    set @a_value_cursor = cursor fast_forward read_only for
    select value from meta_enum_members
    where column_id = @a_column_id;
    
    open @a_value_cursor;
    fetch next from @a_value_cursor into @a_enum_value;
    
    --Searching enum values
    while @@fetch_status = 0
    begin         
        if @a_enum_value_list <> ''
            set @a_enum_value_list = @a_enum_value_list + ' or ';
                     
        set @a_enum_value_list = @a_enum_value_list + @a_column_name + '=' + cast(@a_enum_value as varchar);
        
        fetch next from @a_value_cursor into @a_enum_value;    
    end;
    
    close @a_value_cursor;
    deallocate @a_value_cursor;
         
    if @a_enum_value_list <> ''     
        --Adding enum column constraint
        exec ('alter table tbl_' + @a_table_name
        + ' add constraint m' + @a_column_id_str
        + ' check(' + @a_enum_value_list + ')');
    else
        --(so, one should specify enum members before inserting)
        exec ('alter table tbl_' + @a_table_name
         + ' add constraint m' + @a_column_id_str
         + ' check(' + @a_column_name + ' is null)');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_enum_constraint';

go

--Function af dropping enum values constraint
create procedure p_drop_enum_constraint(
       --Column id
       @a_column_id binary(16))
as
begin
    declare
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);

    --Reading column properties
    select @a_table_id = table_id
    from meta_enum_columns
    where column_id = @a_column_id;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
     
    exec ('alter table tbl_' + @a_table_name
    + ' drop constraint m' + @a_column_id_str);     
end;

go

exec dbo.p_drop_proc_if_exists 'meta_add_enum_column';

go

--Function of creating enum column
create procedure meta_add_enum_column(
    --Table id
    @a_table_id binary(16),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(max))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_not_exists @a_table_id, @a_column_id, @a_column_name, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;

    set @a_table_name =  dbo.p_get_table_name(@a_table_id);
    
    --Changing the metadata
    insert into meta_enum_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (@a_column_id, @a_table_id, @a_column_name,
        dbo.p_generate_order_index(@a_table_id), 1);

    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
         
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add ' + @a_column_name
    + ' numeric(10)');
    
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
         
    --Creating enum values constraint
    exec dbo.p_create_enum_constraint @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_add_enum_member';

go

--Function of adding new enum value
create procedure meta_add_enum_member(
       --Column id
       @a_column_id binary(16),
       --Value
       @a_value numeric(10))
with execute as owner
as
begin
    set nocount on;
    declare
    --Row count
    @a_row_count numeric,
    --Column id str
    @a_column_id_str varchar(32);
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Searching for enum member
    select @a_row_count = count(*)
    from meta_enum_members
    where column_id = @a_column_id and value = @a_value;
    
    if @a_row_count <> 0
    begin
        declare @a_value_str varchar(10);
        set @a_value_str = cast(@a_value as varchar);
        raiserror('A member with the specified value was already added (column_id = %s, value = %s) (#error_code = 20054)',
        16, 1, @a_column_id_str, @a_value_str);
        return;
    end;
    
    set xact_abort on;
    begin tran;

    --Changing the metadata
    insert into meta_enum_members (column_id, value)
    values(@a_column_id, @a_value);

    --Dropping enum constraint
    exec dbo.p_drop_enum_constraint @a_column_id;
         
    --Creating new enum constraint
    exec dbo.p_create_enum_constraint @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_enum_member';

go

--Function of removing enum value
create procedure meta_delete_enum_member(
    --Column id
    @a_column_id binary(16),
    --Value
    @a_value numeric(10))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Default value
    @a_default_value numeric(10),
    --Row count
    @a_row_count numeric,
    --Column id str
    @a_column_id_str varchar(32);
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Searching for enum member
    select @a_row_count = count(*)
    from meta_enum_members
    where column_id = @a_column_id and value = @a_value;
    
    if @a_row_count = 0
    begin
        raiserror('Deleting nonexistent enumeration member is forbidden (column_id = %s) (#error_code = 20018)',
        16, 1, @a_column_id_str);        
        return;
    end;

    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_default_value = default_value
    from meta_enum_columns
    where column_id = @a_column_id;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    set xact_abort on;
    begin tran;
    
    --If default value was deleted
    if @a_value = @a_default_value
        --Setting default value to null
        exec dbo.meta_set_enum_default @a_column_id, null;
    
    --Changing the metadata
    delete from meta_enum_members
    where column_id = @a_column_id
    and value = @a_value;
    
    --Dropping enum constraint
    exec dbo.p_drop_enum_constraint @a_column_id;
         
    --Setting deleted member values to null
    exec ('update tbl_' + @a_table_name
    + ' set ' + @a_column_name
    + ' = null'
    + ' where ' + @a_column_name + ' = ' + @a_value);

    --Creating new enum constraint
    exec dbo.p_create_enum_constraint @a_column_id;

    commit;      
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_enum_member_value';

go

--Function of changing enum member value
create procedure meta_set_enum_member_value(
    --Column name
    @a_column_id binary(16),
    --Old value
    @a_old_value numeric(10),
    --New value
    @a_new_value numeric(10))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Default value
    @a_default_value numeric(10);

    if @a_new_value = @a_old_value
        return;
        
    set xact_abort on;
    begin tran;
        
    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_default_value = default_value
    from meta_enum_columns
    where column_id = @a_column_id;
        
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --If default value is equal to old value
    if @a_default_value = @a_old_value
        --Setting default value to null
        exec dbo.meta_set_enum_default @a_column_id, null;
    
    --Changing the metadata
    update meta_enum_members
    set value = @a_new_value
    where column_id = @a_column_id and value = @a_old_value;
        
    --Dropping old enum contraing
    exec dbo.p_drop_enum_constraint @a_column_id;
                 
    --Updating old member values
    exec ('update tbl_' + @a_table_name
    + ' set ' + @a_column_name
    + ' = ' + @a_new_value
    + ' where ' + @a_column_name
    + ' = ' + @a_old_value);
         
    --Creating new enum constraint
    exec dbo.p_create_enum_constraint @a_column_id;
    
    --If default value is equal to old value
    if @a_default_value = @a_old_value
        --Setting default value to new value
        exec dbo.meta_set_enum_default @a_column_id, @a_new_value;
    
    commit;     
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_enum_default';

go

--Function of setting enum column default value
create procedure meta_set_enum_default(
    --Column id
    @a_column_id binary(16),
    --Default value
    @a_default_value numeric(10))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Result
    @a_result numeric(1),
    --Column id str
    @a_column_id_str varchar(32),
    --Old default value
    @a_old_default_value numeric(10);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_enum_columns', 'enumeration', @a_result output;
    
    if @a_result = 0 return;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_old_default_value = default_value
    from meta_enum_columns
    where column_id = @a_column_id;
    
    --If there is no need for change
    if (@a_old_default_value = @a_default_value) or
    (@a_old_default_value is null and @a_default_value is null)
        return;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    set xact_abort on;
    begin tran;
    
    --If there is an old default value
    if @a_old_default_value is not null
        --Dropping the old default value
        exec ('alter table tbl_' 
        + @a_table_name
        + ' drop constraint def_' + @a_column_id_str);
    
    --Updating the metadata
    update meta_enum_columns
    set default_value = @a_default_value
    where column_id = @a_column_id;
    
    --If there is a new default value
    if @a_default_value is not null
        exec ('alter table tbl_' 
        + @a_table_name
        + ' add constraint def_' + @a_column_id_str
        + ' default ' + @a_default_value
        + ' for ' + @a_column_name);        
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_enum_col_constrs';

go

--Function of creating all enum constraints
create procedure p_create_enum_col_constrs(
    --Column id
    @a_column_id binary(16))
as
begin
    exec dbo.p_create_enum_constraint @a_column_id;
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_enum_col_constrs';

go

--Function of dropping all enum constraints
create procedure p_drop_enum_col_constrs(
    --Column id
    @a_column_id binary(16))
as
begin
    exec dbo.p_drop_enum_constraint @a_column_id;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_enum_value_checking';

go

--Function of enabling/disabling enum value checking
create procedure meta_set_enum_value_checking(
    --The column id
    @a_column_id binary(16),
    --The need for enum value checking
    @a_value_checking numeric(1))
with execute as owner
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric(1),
    --Column id str
    @a_column_id_str varchar(32),
    --Table name
    @a_table_name varchar(26);

    exec dbo.p_check_col_type @a_column_id, 'meta_enum_columns', 'enumeration', @a_result output;
    
    if @a_result = 0 return;
    
    --Reading the table name
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    set xact_abort on;
    begin tran;
    
    --If value checking is enabled
    if @a_value_checking = 1
        exec ('alter table tbl_' + @a_table_name
        + ' with check check constraint m' + @a_column_id_str);
    --If value checking is disabled
    else
        exec ('alter table tbl_' + @a_table_name
        + ' with check nocheck constraint m' + @a_column_id_str);
    
    --Updating the metadata
    update meta_enum_columns
    set value_checking = @a_value_checking
    where column_id = @a_column_id;
    
    commit;
end;

--5-----------------------------------------------------------------
go

exec dbo.p_drop_proc_if_exists 'meta_add_decimal_column';

go

--Function of adding decimal column
create procedure meta_add_decimal_column(
    --Table id
    @a_table_id binary(16),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(max))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_not_exists @a_table_id, @a_column_id, @a_column_name, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    set @a_table_name =  dbo.p_get_table_name(@a_table_id);
    
    --Changing the metadata
    insert into meta_decimal_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (@a_column_id, @a_table_id, @a_column_name,
        dbo.p_generate_order_index(@a_table_id), 1);     

    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
     
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add ' + @a_column_name
    + ' numeric');

    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_decimal_minimum';

go

--Function of setting decimal column minimum
create procedure meta_set_decimal_minimum(
    --Column id
    @a_column_id binary(16),
    --Minimum
    @a_minimum numeric(38,5))
with execute as owner
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric(1),
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Old minimum
    @a_old_minimum numeric(38,5),
    --Column id str
    @a_column_id_str varchar(32);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_decimal_columns', 'decimal', @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_id = table_id,
    @a_column_name = column_name,
    @a_old_minimum = minimum
    from meta_decimal_columns
    where column_id = @a_column_id;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Updating the metadata
    if @a_minimum is not null
        exec ('update meta_decimal_columns'
        + ' set minimum = ' + @a_minimum
        + ' where column_id = 0x' + @a_column_id_str);
    else
        exec ('update meta_decimal_columns'
        + ' set minimum = null'
        + ' where column_id = 0x' + @a_column_id_str);
     
    --There is no need to drop table view
     
    if @a_old_minimum is not null
        --Drop old constraint 
        exec dbo.p_drop_decimal_minimum_check @a_column_id;

    --Create new check
    if @a_minimum is not null
        exec dbo.p_create_decimal_minimum_check @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_decimal_minimum_check';

go

--Function of creating decimal minimum check
create procedure p_create_decimal_minimum_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Table name
    @a_table_name varchar(26),
    --Minimum
    @a_minimum numeric(38,5),
    --Column name
    @a_column_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);

    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_minimum = minimum,
    @a_column_name = column_name
    from meta_decimal_columns
    where column_id = @a_column_id;
    
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add constraint n' + @a_column_id_str
    + ' check(' + @a_column_name
    + ' >= ' + @a_minimum + ')');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_decimal_minimum_check';

go

--Function of creating decimal minimum check
create procedure p_drop_decimal_minimum_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Table name
    @a_table_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);

    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    exec ('alter table tbl_' 
    + @a_table_name
    + ' drop constraint n' + @a_column_id_str);
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_decimal_maximum';

go

--Function of setting decimal column maximum
create procedure meta_set_decimal_maximum(
    --Column id
    @a_column_id binary(16),
    --Maximum
    @a_maximum numeric(38,5))
with execute as owner
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric(1),
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Old maximum
    @a_old_maximum numeric(38,5),
    --Column type
    @a_column_type varchar(30),
    --Column id str
    @a_column_id_str varchar(32);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_decimal_columns', 'decimal', @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_old_maximum = maximum,
    @a_column_name = column_name,
    @a_table_id = table_id
    from meta_decimal_columns
    where column_id = @a_column_id;
            
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Updating the metadata
    if @a_maximum is not null
        exec ('update meta_decimal_columns'
        + ' set maximum = ' + @a_maximum
        + ' where column_id = 0x' + @a_column_id_str);
    else
        exec ('update meta_decimal_columns'
        + ' set maximum = null'
        + ' where column_id = 0x' + @a_column_id_str);
         
    --There is no need to drop table view
         
    if @a_old_maximum is not null
        --Drop old constraint 
        exec dbo.p_drop_decimal_maximum_check @a_column_id;
         
    --Create new check
    if @a_maximum is not null
        exec dbo.p_create_decimal_maximum_check @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_decimal_maximum_check';

go

--Function of creating decimal maximum check
create procedure p_create_decimal_maximum_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Table name
    @a_table_name varchar(26),
    --Maximum
    @a_maximum numeric(38,5),
    --Column name
    @a_column_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);

    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_maximum = maximum,
    @a_column_name = column_name
    from meta_decimal_columns
    where column_id = @a_column_id;
    
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add constraint x' + @a_column_id_str
    + ' check(' + @a_column_name
    + ' <= ' + @a_maximum + ')');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_decimal_maximum_check';

go

--Function of creating decimal maximum check
create procedure p_drop_decimal_maximum_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Table name
    @a_table_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);

    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    exec ('alter table tbl_' 
    + @a_table_name
    + ' drop constraint x' + @a_column_id_str);
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_decimal_precision';

go

--Function of setting decimal column precision
create procedure meta_set_decimal_precision(
    --Column id
    @a_column_id binary(16),
    --Precision
    @a_precision numeric(2))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Old precision
    @a_old_precision numeric(2),
    --Old scale
    @a_old_scale numeric(2),
    --Is not null
    @a_is_not_null numeric(1),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_decimal_columns', 'decimal', @a_result output;
    
    if @a_result = 0 return;
    
    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_old_precision = precision, @a_old_scale = scale,
    @a_is_not_null = is_not_null
    from meta_decimal_columns
    where column_id = @a_column_id;
    
    --If there is no need for change
    if (@a_precision = @a_old_precision)
        or (@a_precision is null and @a_old_precision is null)
        return;
    
    set xact_abort on;
    begin tran;
         
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
    
    --Dropping all column indexes (otherwise SQL Server refuses to alter the column)
    exec dbo.p_delete_column_indexes @a_column_id, /*Saving the index list*/ 1;
    
    --Alter column sql
    declare @a_alter_sql varchar(max);
      
    --If precision is not specified
    if @a_precision is null
        set @a_alter_sql = 'alter table tbl_' 
        + @a_table_name
        + ' alter column ' + @a_column_name
        + ' numeric';
    --If scale is not specified
    else if @a_old_scale is null
        set @a_alter_sql = 'alter table tbl_' 
        + @a_table_name
        + ' alter column ' + @a_column_name
        + ' numeric(' + cast(@a_precision as varchar) + ')';
    --If precision and scale are specified
    else if @a_old_scale is not null
        set @a_alter_sql = 'alter table tbl_' 
        + @a_table_name
        + ' alter column ' + @a_column_name
        + ' numeric(' + cast(@a_precision as varchar) + ','
        + cast(@a_old_scale as varchar) + ')';
        
    if @a_is_not_null = 1
		set @a_alter_sql = @a_alter_sql + ' not null';
		
	exec (@a_alter_sql);
         
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
    
    --Recreating the column indexes
	exec dbo.p_recreate_column_indexes @a_column_id;
    
    --Changing the metadata
    update meta_decimal_columns
    set precision = @a_precision
    where column_id = @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_decimal_scale';

go

--Function of setting decimal column scale
create procedure meta_set_decimal_scale(
    --Column id
    @a_column_id binary(16),
    --Scale
    @a_scale numeric(2))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Old precision
    @a_old_precision numeric(2),
    --Old scale
    @a_old_scale numeric(2),
    --Is not null
    @a_is_not_null numeric(1),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_decimal_columns', 'decimal', @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_old_precision = precision, @a_old_scale = scale,
    @a_is_not_null = is_not_null
    from meta_decimal_columns
    where column_id = @a_column_id;
    
    --If there is no need for change
    if (@a_scale = @a_old_scale) 
        or (@a_scale is null and @a_old_scale is null)
    begin
		commit;
        return;
    end;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Changing the metadata
    update meta_decimal_columns
    set scale = @a_scale
    where column_id = @a_column_id;
         
    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
    
    --Dropping all column indexes (otherwise SQL Server refuses to alter the column)
    exec dbo.p_delete_column_indexes @a_column_id, /*Saving the index list*/ 1;
    
    --Alter column sql
    declare @a_alter_column_sql varchar(max);
    
    if @a_old_precision is not null
    begin
		--If scale is not specified
		if @a_scale is null
			set @a_alter_column_sql = 'alter table tbl_' 
			+ @a_table_name
			+ ' alter column ' + @a_column_name
			+ ' numeric(' + cast(@a_old_precision as varchar) + ')';
		--If precision and scale are specified
		else if @a_scale is not null
			set @a_alter_column_sql = 'alter table tbl_' 
			+ @a_table_name
			+ ' alter column ' + @a_column_name
			+ ' numeric(' + cast(@a_old_precision as varchar)
			+ ',' + cast(@a_scale as varchar) + ')';
			
		if @a_is_not_null = 1
		set @a_alter_column_sql = @a_alter_column_sql + ' not null';
		
		exec (@a_alter_column_sql);
    end;
         
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
    
    --Recreating the column indexes
	exec dbo.p_recreate_column_indexes @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_decimal_default';

go

--Function of setting column default value
create procedure meta_set_decimal_default(
    --Column id
    @a_column_id binary(16),
    --Default value
    @a_default_value numeric(38,5))
with execute as owner
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_decimal_columns', 'decimal', @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    exec dbo.p_set_numeric_default @a_column_id, @a_default_value;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_set_numeric_default';

go

--Function of setting numeric default value
create procedure p_set_numeric_default(
    --Column id
    @a_column_id binary(16),
    --Default value
    @a_default_value numeric(38,5))
as
begin
    declare
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Column type
    @a_column_type varchar(30),
    --Column id str
    @a_column_id_str varchar(32),
    --Old default value
    @a_old_default_value numeric(38,5);
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column type
    select @a_column_type = column_type
    from meta_table_columns
    where column_id = @a_column_id;
    
    --Reading column properties
    declare @read_col_props_sql nvarchar(500);
    set @read_col_props_sql = 'select @a_table_id_out = table_id,'
    + ' @a_column_name_out = column_name, '
    + ' @a_default_value_out = default_value'
    + ' from ' + @a_column_type
    + ' where column_id = @a_column_id';

    exec sp_executesql @read_col_props_sql,
    N'@a_column_id binary(16), @a_table_id_out binary(16) output, @a_column_name_out varchar(26) output, @a_default_value_out numeric(38,5) output', 
    @a_column_id = @a_column_id,
    @a_table_id_out = @a_table_id output,
    @a_column_name_out = @a_column_name output,
    @a_default_value_out = @a_old_default_value output;
    
    --If there is no need for change
    if (@a_old_default_value = @a_default_value) or
    (@a_old_default_value is null and @a_default_value is null)
        return;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --If there is an old default value
    if @a_old_default_value is not null
        --Dropping the old default value
        exec ('alter table tbl_' 
            + @a_table_name
            + ' drop constraint def_' + @a_column_id_str);
    
    --Updating the metadata
    if @a_default_value is not null
        exec ('update ' + @a_column_type
        + ' set default_value = ' + @a_default_value
        + ' where column_id = 0x' + @a_column_id_str);
    else
        exec ('update ' + @a_column_type
        + ' set default_value = null'
        + ' where column_id = 0x' + @a_column_id_str);
    
    --If there is a new default value  
    if @a_default_value is not null
        exec ('alter table tbl_' 
        + @a_table_name
        + ' add constraint def_' + @a_column_id_str
        + ' default ' + @a_default_value + ' for ' + @a_column_name);
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_decimal_col_constrs';

go

--Function of creating all decimal column constraints
create procedure p_create_decimal_col_constrs(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Minimum
    @a_minimum numeric(38,5),
    --Maximum
    @a_maximum numeric(38,5);
    
    select @a_minimum = minimum,
    @a_maximum = maximum
    from meta_decimal_columns
    where column_id = @a_column_id;
    
    if @a_minimum is not null
        exec dbo.p_create_decimal_minimum_check @a_column_id;
    if @a_maximum is not null
        exec dbo.p_create_decimal_maximum_check @a_column_id;
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_decimal_col_constrs';

go

--Function of dropping all decimal column constraints
create procedure p_drop_decimal_col_constrs(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Minimum
    @a_minimum numeric(38,5),
    --Maximum
    @a_maximum numeric(38,5);
    
    select @a_minimum = minimum,
    @a_maximum = maximum
    from meta_decimal_columns
    where column_id = @a_column_id;
    
    if @a_minimum is not null
        exec dbo.p_drop_decimal_minimum_check @a_column_id;
        
    if @a_maximum is not null
        exec dbo.p_drop_decimal_maximum_check @a_column_id;
end;

--6-----------------------------------------------------------------------
go

exec dbo.p_drop_proc_if_exists 'meta_add_datetime_column';

go

--Function of adding new datetime column
create procedure meta_add_datetime_column(
    --Table id
    @a_table_id binary(16),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(max))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);

    exec dbo.p_check_col_not_exists @a_table_id, @a_column_id, @a_column_name, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;

    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Changing the metadata
    insert into meta_datetime_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (@a_column_id, @a_table_id, @a_column_name,
        dbo.p_generate_order_index(@a_table_id), 1);
        
    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
         
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add ' + @a_column_name
    + ' datetime');
         
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_datetime_minimum';

go

--Function of setting datetime column minimum
create procedure meta_set_datetime_minimum(
    --Column id
    @a_column_id binary(16),
    --Minimum
    @a_minimum datetime)
with execute as owner
as
begin
    set nocount on;
    declare
    --Old minimum
    @a_old_minimum datetime,
    --Customization level
    @a_customization_level numeric(10),
    --Result
    @a_result numeric(1),
    --Column id str
    @a_column_id_str varchar(32);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_datetime_columns', 'datetime', @a_result output;
    
    if @a_result = 0 return;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_old_minimum = minimum,
    @a_customization_level = customization_level
    from meta_datetime_columns
    where column_id = @a_column_id;
    
    --If this is a system column
    if @a_customization_level = 0
    begin
        raiserror('Changing datetime system column minimum is forbidden (column_id = %s) (#error_code = 20008)',
        16, 1, @a_column_id_str);
        return;
    end;
    
    --If there is no need for change
    if (@a_minimum = @a_old_minimum) or
        (@a_minimum is null and @a_old_minimum is null)
        return;
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    update meta_datetime_columns
    set minimum = @a_minimum
    where column_id = @a_column_id;
         
    --There is no need to drop table view
    
    if @a_old_minimum is not null
        --Drop old constraint
        exec dbo.p_drop_datetime_minimum_check @a_column_id;         
    
    if @a_minimum is not null
        exec dbo.p_create_datetime_minimum_check @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_datetime_minimum_check';

go

--Function of creating datetime minimum check
create procedure p_create_datetime_minimum_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Column id str
    @a_column_id_str varchar(32),
    --Table name
    @a_table_name varchar(26),
    --Minimum
    @a_minimum datetime,
    --Minimum str
    @a_minimum_str varchar(64),
    --Column name
    @a_column_name varchar(26);
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_column_name = column_name,
    @a_minimum = minimum
    from meta_datetime_columns
    where column_id = @a_column_id;
    
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    set @a_minimum_str = convert(varchar, @a_minimum, 9);

    exec ('alter table tbl_' 
    + @a_table_name
    + ' add constraint n' + @a_column_id_str
    + ' check(' + @a_column_name
    + ' >= ''' + @a_minimum_str + ''')');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_datetime_minimum_check';

go

--Function of dropping datetime minimum check
create procedure p_drop_datetime_minimum_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Table name
    @a_table_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    exec ('alter table tbl_' 
    + @a_table_name
    + ' drop constraint n' + @a_column_id_str); 
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_datetime_maximum';

go

--Function of setting datetime column maximum
create procedure meta_set_datetime_maximum(
    --Column id
    @a_column_id binary(16),
    --Minimum
    @a_maximum datetime)
with execute as owner
as
begin
    set nocount on;
    declare
    --Old maximum
    @a_old_maximum datetime,
    --Customization level
    @a_customization_level numeric(10),
    --Result
    @a_result numeric(1),
    --Column id str
    @a_column_id_str varchar(32);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_datetime_columns', 'datetime', @a_result output;
    
    if @a_result = 0 return;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_old_maximum = maximum,
    @a_customization_level = customization_level
    from meta_datetime_columns
    where column_id = @a_column_id;
    
    --If this is a system column
    if @a_customization_level = 0
    begin
        raiserror('Changing datetime system column maximum is forbidden (column_id = %s) (#error_code = 20008)',
        16, 1, @a_column_id_str);
        return;
    end;
    
    --If there is no need for change
    if (@a_maximum = @a_old_maximum) or
        (@a_maximum is null and @a_old_maximum is null)
        return;
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    update meta_datetime_columns
    set maximum = @a_maximum
    where column_id = @a_column_id;
         
    --There is no need to drop table view
    
    if @a_old_maximum is not null
        --Drop old constraint
        exec dbo.p_drop_datetime_maximum_check @a_column_id;
    
    if @a_maximum is not null
        exec dbo.p_create_datetime_maximum_check @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_datetime_maximum_check';

go

--Function of creating datetime maximum check
create procedure p_create_datetime_maximum_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Column id str
    @a_column_id_str varchar(32),
    --Table name
    @a_table_name varchar(26),
    --Maximum
    @a_maximum datetime,
    --Maximum str
    @a_maximum_str varchar(64),
    --Column name
    @a_column_name varchar(26);
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_column_name = column_name,
    @a_maximum = maximum
    from meta_datetime_columns
    where column_id = @a_column_id;
    
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    set @a_maximum_str = convert(varchar, @a_maximum, 9);

    exec ('alter table tbl_' 
    + @a_table_name
    + ' add constraint x' + @a_column_id_str
    + ' check(' + @a_column_name
    + ' <= ''' + @a_maximum_str + ''')');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_datetime_maximum_check';

go

--Function of dropping datetime maximum check
create procedure p_drop_datetime_maximum_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Table name
    @a_table_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    exec ('alter table tbl_' 
    + @a_table_name
    + ' drop constraint x' + @a_column_id_str); 
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_datetime_default';

go

--Function of setting column default value
create procedure meta_set_datetime_default(
    --Column id
    @a_column_id binary(16),
    --Default value
    @a_default_value datetime)
with execute as owner
as
begin
    set nocount on;
    declare
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Default value str
    @a_default_value_str varchar(80),
    --Customization level
    @a_customization_level numeric(10),
    --Result
    @a_result numeric(1),
    --Old default value
    @a_old_default_value datetime,
    --Column id str
    @a_column_id_str varchar(32);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_datetime_columns', 'datetime', @a_result output;
    
    if @a_result = 0 return;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_customization_level = customization_level,
    @a_old_default_value = default_value
    from meta_datetime_columns
    where column_id = @a_column_id;
    
    --If this is a system column
    if @a_customization_level = 0
    begin
        raiserror('Changing datetime system column default value is forbidden (column_id = %s) (#error_code = 20008)',
        16, 1, @a_column_id_str);
        return;
    end;
    
    --If there is no need for change
    if (@a_old_default_value = @a_default_value) or
    (@a_old_default_value is null and @a_default_value is null)
        return;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    set xact_abort on;
    begin tran;
    
    --If there is an old default value
    if @a_old_default_value is not null
        exec ('alter table tbl_' 
        + @a_table_name
        + ' drop constraint def_' + @a_column_id_str);
    
    --Updating the metadata
    update meta_datetime_columns
    set default_value = @a_default_value
    where column_id = @a_column_id;
    
    --If there is a new default value
    if @a_default_value is not null
    begin
        set @a_default_value_str = convert(varchar, @a_default_value, 9);
                
        exec ('alter table tbl_' 
        + @a_table_name
        + ' add constraint def_' + @a_column_id_str
        + ' default ''' + @a_default_value_str + ''' for ' + @a_column_name);
    end;       
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_datetime_col_constrs';

go

--Function of creating all datetime column constraints
create procedure p_create_datetime_col_constrs(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Minimum
    @a_minimum datetime,
    --Maximum
    @a_maximum datetime;
    
    select @a_minimum = minimum,
    @a_maximum = maximum
    from meta_datetime_columns
    where column_id = @a_column_id;
    
    if @a_minimum is not null
        exec dbo.p_create_datetime_minimum_check @a_column_id;
    if @a_maximum is not null
        exec dbo.p_create_datetime_maximum_check @a_column_id;
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_datetime_col_constrs';

go

--Function of droppping all datetime column constraints
create procedure p_drop_datetime_col_constrs(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Minimum
    @a_minimum datetime,
    --Maximum
    @a_maximum datetime;
    
    select @a_minimum = minimum,
    @a_maximum = maximum
    from meta_datetime_columns
    where column_id = @a_column_id;
    
    if @a_minimum is not null
        exec dbo.p_drop_datetime_minimum_check @a_column_id;
    if @a_maximum is not null
        exec dbo.p_drop_datetime_maximum_check @a_column_id;
end;

--7-----------------------------------------------------------------------
go

exec dbo.p_drop_proc_if_exists 'meta_add_bool_column';

go

--Function of creating boolean column
create procedure meta_add_bool_column(
    --Table id
    @a_table_id binary(16),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(max))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);    
    
    exec dbo.p_check_col_not_exists @a_table_id, @a_column_id, @a_column_name, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
         
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
         
    --Changing the metadata
    insert into meta_bool_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (@a_column_id, @a_table_id, @a_column_name,
        dbo.p_generate_order_index(@a_table_id), 1);
         
    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
         
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add  ' + @a_column_name
    + ' numeric(1)');
    
    --Creating value constraint
    exec dbo.p_create_bool_col_check @a_column_id;
         
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_bool_col_check';

go

--Function of creating bool column check
create procedure p_create_bool_col_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Column name
    @a_column_name varchar(26),
    --Table name
    @a_table_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_column_name = column_name,
    @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    exec ('alter table tbl_'
    + @a_table_name
    + ' add constraint b' + @a_column_id_str
    + ' check(' + @a_column_name + '=0 or ' 
    + @a_column_name + '=1)');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_bool_col_check';

go

--Function of droping a bool column check
create procedure p_drop_bool_col_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Column id str
    @a_column_id_str varchar(32),
    --Table name
    @a_table_name varchar(32);
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    exec ('alter table tbl_' + @a_table_name
    + ' drop constraint b' + @a_column_id_str);
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_bool_default';

go

--Function of setting column default value
create procedure meta_set_bool_default(
    --Column id
    @a_column_id binary(16),
    --Default value
    @a_default_value numeric(1))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Customization level
    @a_customization_level numeric(10),
    --Result
    @a_result numeric(1),
    --Old default value
    @a_old_default_value numeric(1),
    --Column id str
    @a_column_id_str varchar(32);    
    
    exec dbo.p_check_col_type @a_column_id, 'meta_bool_columns', 'bool', @a_result output;
    
    if @a_result = 0 return;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_customization_level = customization_level,
    @a_old_default_value = default_value
    from meta_bool_columns
    where column_id = @a_column_id;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --If this is a system column
    if @a_customization_level = 0
    begin    
        raiserror('Changing bool system column default value is forbidden (column_id = %s) (#error_code = 20008)',
        16, 1, @a_column_id_str);
        return;
    end;
    
    --If there is no need for change
    if (@a_old_default_value = @a_default_value) or
    (@a_old_default_value is null and @a_default_value is null)
        return;
    
    set xact_abort on;
    begin tran;
    
    --If there is an old default value
    if @a_old_default_value is not null
        --Dropping the old default value
        exec ('alter table tbl_' 
        + @a_table_name
        + ' drop constraint def_' + @a_column_id_str);
    
    --Updating the metadata
    update meta_bool_columns
    set default_value = @a_default_value
    where column_id = @a_column_id;
    
    --If there is a new default value
    if @a_default_value is not null
        exec ('alter table tbl_' 
        + @a_table_name
        + ' add constraint def_' + @a_column_id_str
        + ' default ' + @a_default_value + ' for ' + @a_column_name);
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_bool_col_constrs';

go

--Function of creating all bool column constraints
create procedure p_create_bool_col_constrs(
    --Column id
    @a_column_id binary(16))
as
begin
    exec dbo.p_create_bool_col_check @a_column_id;
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_bool_col_constrs';

go

--Function of dropping all bool column constraints
create procedure p_drop_bool_col_constrs(
    --Column id
    @a_column_id binary(16))
as
begin
    exec dbo.p_drop_bool_col_check @a_column_id;
end;

--8-----------------------------------------------------------------------
go

exec dbo.p_drop_proc_if_exists 'meta_add_binary_column';

go

--Function of creating binary column
create procedure meta_add_binary_column(
    --Table id
    @a_table_id binary(16),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(max))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_not_exists @a_table_id, @a_column_id, @a_column_name, @a_result;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
         
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
         
    --Changing the metadata
    insert into meta_binary_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (@a_column_id, @a_table_id, @a_column_name,
        dbo.p_generate_order_index(@a_table_id), 1);
         
    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
         
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add  ' + @a_column_name
    + ' varbinary(max)');
         
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
    
    commit;
end;

go

--9----------------------------------------------------------------------
go

exec dbo.p_drop_proc_if_exists 'meta_add_int_column';

go

--Function of adding new integer column
create procedure meta_add_int_column(
    --Table id
    @a_table_id binary(16),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(max))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_not_exists @a_table_id, @a_column_id, @a_column_name, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;

    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Changing the metadata
    insert into meta_int_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (@a_column_id, @a_table_id, @a_column_name,
        dbo.p_generate_order_index(@a_table_id), 1);
        
    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
         
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add ' + @a_column_name
    + ' numeric(10)');
         
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;

    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_int_default';

go

--Function of setting column default value
create procedure meta_set_int_default(
    --Column id
    @a_column_id binary(16),
    --Default value
    @a_default_value numeric(10))
with execute as owner
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_int_columns', 'int', @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    exec dbo.p_set_numeric_default @a_column_id, @a_default_value;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_int_maximum';

go

--Function of setting int column maximum
create procedure meta_set_int_maximum(
    --Column id
    @a_column_id binary(16),
    --Maximum
    @a_maximum numeric(10))
with execute as owner
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric(1),
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Old maximum
    @a_old_maximum numeric(38,5),
    --Column type
    @a_column_type varchar(30),
    --Column id str
    @a_column_id_str varchar(32);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_int_columns', 'int', @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_old_maximum = maximum,
    @a_column_name = column_name,
    @a_table_id = table_id
    from meta_int_columns
    where column_id = @a_column_id;
            
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Updating the metadata
    if @a_maximum is not null
        exec ('update meta_int_columns'
        + ' set maximum = ' + @a_maximum
        + ' where column_id = 0x' + @a_column_id_str);
    else
        exec ('update meta_int_columns'
        + ' set maximum = null'
        + ' where column_id = 0x' + @a_column_id_str);
         
    --There is no need to drop table view
         
    if @a_old_maximum is not null
        --Drop old constraint 
        exec dbo.p_drop_int_maximum_check @a_column_id;
         
    --Create new check
    if @a_maximum is not null
        exec dbo.p_create_int_maximum_check @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_int_maximum_check';

go

--Function of creating int maximum check
create procedure p_create_int_maximum_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Table name
    @a_table_name varchar(26),
    --Maximum
    @a_maximum numeric(10),
    --Column name
    @a_column_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);

    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_maximum = maximum,
    @a_column_name = column_name
    from meta_int_columns
    where column_id = @a_column_id;
    
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add constraint x' + @a_column_id_str
    + ' check(' + @a_column_name
    + ' <= ' + @a_maximum + ')');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_int_maximum_check';

go

--Function of creating int maximum check
create procedure p_drop_int_maximum_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Table name
    @a_table_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);

    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    exec ('alter table tbl_' 
    + @a_table_name
    + ' drop constraint x' + @a_column_id_str);
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_int_minimum';

go

--Function of setting int column minimum
create procedure meta_set_int_minimum(
    --Column id
    @a_column_id binary(16),
    --Minimum
    @a_minimum numeric(10))
with execute as owner
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric(1),
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Old minimum
    @a_old_minimum numeric(10),
    --Column id str
    @a_column_id_str varchar(32);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_int_columns', 'int', @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_id = table_id,
    @a_column_name = column_name,
    @a_old_minimum = minimum
    from meta_int_columns
    where column_id = @a_column_id;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Updating the metadata
    if @a_minimum is not null
        exec ('update meta_int_columns'
        + ' set minimum = ' + @a_minimum
        + ' where column_id = 0x' + @a_column_id_str);
    else
        exec ('update meta_int_columns'
        + ' set minimum = null'
        + ' where column_id = 0x' + @a_column_id_str);
     
    --There is no need to drop table view
     
    if @a_old_minimum is not null
        --Drop old constraint 
        exec dbo.p_drop_int_minimum_check @a_column_id;

    --Create new check
    if @a_minimum is not null
        exec dbo.p_create_int_minimum_check @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_int_minimum_check';

go

--Function of creating int minimum check
create procedure p_create_int_minimum_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Table name
    @a_table_name varchar(26),
    --Minimum
    @a_minimum numeric(10),
    --Column name
    @a_column_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);

    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_minimum = minimum,
    @a_column_name = column_name
    from meta_int_columns
    where column_id = @a_column_id;
    
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add constraint n' + @a_column_id_str
    + ' check(' + @a_column_name
    + ' >= ' + @a_minimum + ')');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_int_minimum_check';

go

--Function of creating int minimum check
create procedure p_drop_int_minimum_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Table name
    @a_table_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);

    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    exec ('alter table tbl_' 
    + @a_table_name
    + ' drop constraint n' + @a_column_id_str);
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_int_col_constrs';

go

--Function of creating all int column constraints
create procedure p_create_int_col_constrs(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Minimum
    @a_minimum numeric(10),
    --Maximum
    @a_maximum numeric(10);
    
    select @a_minimum = minimum,
    @a_maximum = maximum
    from meta_int_columns
    where column_id = @a_column_id;
    
    if @a_minimum is not null
        exec dbo.p_create_int_minimum_check @a_column_id;
    if @a_maximum is not null
        exec dbo.p_create_int_maximum_check @a_column_id;
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_int_col_constrs';

go

--Function of dropping all int column constraints
create procedure p_drop_int_col_constrs(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Minimum
    @a_minimum numeric(10),
    --Maximum
    @a_maximum numeric(10);
    
    select @a_minimum = minimum,
    @a_maximum = maximum
    from meta_int_columns
    where column_id = @a_column_id;
    
    if @a_minimum is not null
        exec dbo.p_drop_int_minimum_check @a_column_id;
        
    if @a_maximum is not null
        exec dbo.p_drop_int_maximum_check @a_column_id;
end;

--10-----------------------------------------------------------------
go

exec dbo.p_drop_proc_if_exists 'meta_add_big_int_column';

go

--Function of adding new big integer column
create procedure meta_add_big_int_column(
    --Table id
    @a_table_id binary(16),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(max))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_not_exists @a_table_id, @a_column_id, @a_column_name, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;

    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Changing the metadata
    insert into meta_big_int_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (@a_column_id, @a_table_id, @a_column_name,
        dbo.p_generate_order_index(@a_table_id), 1);
        
    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
         
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add ' + @a_column_name
    + ' numeric(20)');
         
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;

    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_big_int_default';

go

--Function of setting column default value
create procedure meta_set_big_int_default(
    --Column id
    @a_column_id binary(16),
    --Default value
    @a_default_value numeric(20))
with execute as owner
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_big_int_columns', 'big int', @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    exec dbo.p_set_numeric_default @a_column_id, @a_default_value;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_big_int_maximum';

go

--Function of setting big int column maximum
create procedure meta_set_big_int_maximum(
    --Column id
    @a_column_id binary(16),
    --Maximum
    @a_maximum numeric(20))
with execute as owner
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric(1),
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Old maximum
    @a_old_maximum numeric(20),
    --Column type
    @a_column_type varchar(30),
    --Column id str
    @a_column_id_str varchar(32);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_big_int_columns', 'big int', @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_old_maximum = maximum,
    @a_column_name = column_name,
    @a_table_id = table_id
    from meta_big_int_columns
    where column_id = @a_column_id;
            
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Updating the metadata
    if @a_maximum is not null
        exec ('update meta_big_int_columns'
        + ' set maximum = ' + @a_maximum
        + ' where column_id = 0x' + @a_column_id_str);
    else
        exec ('update meta_big_int_columns'
        + ' set maximum = null'
        + ' where column_id = 0x' + @a_column_id_str);
         
    --There is no need to drop table view
         
    if @a_old_maximum is not null
        --Drop old constraint 
        exec dbo.p_drop_big_int_maximum_check @a_column_id;
         
    --Create new check
    if @a_maximum is not null
        exec dbo.p_create_big_int_maximum_check @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_big_int_maximum_check';

go

--Function of creating big int maximum check
create procedure p_create_big_int_maximum_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Table name
    @a_table_name varchar(26),
    --Maximum
    @a_maximum numeric(20),
    --Column name
    @a_column_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);

    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_maximum = maximum,
    @a_column_name = column_name
    from meta_big_int_columns
    where column_id = @a_column_id;
    
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add constraint x' + @a_column_id_str
    + ' check(' + @a_column_name
    + ' <= ' + @a_maximum + ')');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_big_int_maximum_check';

go

--Function of creating big int maximum check
create procedure p_drop_big_int_maximum_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Table name
    @a_table_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);

    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    exec ('alter table tbl_' 
    + @a_table_name
    + ' drop constraint x' + @a_column_id_str);
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_big_int_minimum';

go

--Function of setting big int column minimum
create procedure meta_set_big_int_minimum(
    --Column id
    @a_column_id binary(16),
    --Minimum
    @a_minimum numeric(20))
with execute as owner
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric(1),
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Old minimum
    @a_old_minimum numeric(20),
    --Column id str
    @a_column_id_str varchar(32);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_big_int_columns', 'big int', @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_id = table_id,
    @a_column_name = column_name,
    @a_old_minimum = minimum
    from meta_big_int_columns
    where column_id = @a_column_id;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Updating the metadata
    if @a_minimum is not null
        exec ('update meta_big_int_columns'
        + ' set minimum = ' + @a_minimum
        + ' where column_id = 0x' + @a_column_id_str);
    else
        exec ('update meta_big_int_columns'
        + ' set minimum = null'
        + ' where column_id = 0x' + @a_column_id_str);
     
    --There is no need to drop table view
     
    if @a_old_minimum is not null
        --Drop old constraint 
        exec dbo.p_drop_big_int_minimum_check @a_column_id;

    --Create new check
    if @a_minimum is not null
        exec dbo.p_create_big_int_minimum_check @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_big_int_minimum_check';

go

--Function of creating big int minimum check
create procedure p_create_big_int_minimum_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Table name
    @a_table_name varchar(26),
    --Minimum
    @a_minimum numeric(20),
    --Column name
    @a_column_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);

    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_minimum = minimum,
    @a_column_name = column_name
    from meta_big_int_columns
    where column_id = @a_column_id;
    
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add constraint n' + @a_column_id_str
    + ' check(' + @a_column_name
    + ' >= ' + @a_minimum + ')');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_big_int_minimum_check';

go

--Function of creating big int minimum check
create procedure p_drop_big_int_minimum_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Table name
    @a_table_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);

    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    exec ('alter table tbl_' 
    + @a_table_name
    + ' drop constraint n' + @a_column_id_str);
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_big_int_col_constrs';

go

--Function of creating all big int column constraints
create procedure p_create_big_int_col_constrs(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Minimum
    @a_minimum numeric(20),
    --Maximum
    @a_maximum numeric(20);
    
    select @a_minimum = minimum,
    @a_maximum = maximum
    from meta_big_int_columns
    where column_id = @a_column_id;
    
    if @a_minimum is not null
        exec dbo.p_create_big_int_minimum_check @a_column_id;
    if @a_maximum is not null
        exec dbo.p_create_big_int_maximum_check @a_column_id;
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_big_int_col_constrs';

go

--Function of dropping all big int column constraints
create procedure p_drop_big_int_col_constrs(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Minimum
    @a_minimum numeric(20),
    --Maximum
    @a_maximum numeric(20);
    
    select @a_minimum = minimum,
    @a_maximum = maximum
    from meta_big_int_columns
    where column_id = @a_column_id;
    
    if @a_minimum is not null
        exec dbo.p_drop_big_int_minimum_check @a_column_id;
        
    if @a_maximum is not null
        exec dbo.p_drop_big_int_maximum_check @a_column_id;
end;

--11--------------------------------------------------------------------
go

exec dbo.p_drop_proc_if_exists 'meta_add_small_int_column';

go

--Function of adding new small integer column
create procedure meta_add_small_int_column(
    --Table id
    @a_table_id binary(16),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(max))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_not_exists @a_table_id, @a_column_id, @a_column_name, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;

    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Changing the metadata
    insert into meta_small_int_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (@a_column_id, @a_table_id, @a_column_name,
        dbo.p_generate_order_index(@a_table_id), 1);
        
    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
         
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add ' + @a_column_name
    + ' numeric(5)');
         
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_small_int_default';

go

--Function of setting column default value
create procedure meta_set_small_int_default(
    --Column id
    @a_column_id binary(16),
    --Default value
    @a_default_value numeric(5))
with execute as owner
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_small_int_columns', 'small int', @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    exec dbo.p_set_numeric_default @a_column_id, @a_default_value;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_small_int_maximum';

go

--Function of setting small int column maximum
create procedure meta_set_small_int_maximum(
    --Column id
    @a_column_id binary(16),
    --Maximum
    @a_maximum numeric(5))
with execute as owner
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric(1),
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Old maximum
    @a_old_maximum numeric(5),
    --Column type
    @a_column_type varchar(30),
    --Column id str
    @a_column_id_str varchar(32);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_small_int_columns', 'small int', @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_old_maximum = maximum,
    @a_column_name = column_name,
    @a_table_id = table_id
    from meta_small_int_columns
    where column_id = @a_column_id;
            
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Updating the metadata
    if @a_maximum is not null
        exec ('update meta_small_int_columns'
        + ' set maximum = ' + @a_maximum
        + ' where column_id = 0x' + @a_column_id_str);
    else
        exec ('update meta_small_int_columns'
        + ' set maximum = null'
        + ' where column_id = 0x' + @a_column_id_str);
         
    --There is no need to drop table view
         
    if @a_old_maximum is not null
        --Drop old constraint 
        exec dbo.p_drop_small_int_maximum_check @a_column_id;
         
    --Create new check
    if @a_maximum is not null
        exec dbo.p_create_small_int_maximum_check @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_small_int_maximum_check';

go

--Function of creating small int maximum check
create procedure p_create_small_int_maximum_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Table name
    @a_table_name varchar(26),
    --Maximum
    @a_maximum numeric(5),
    --Column name
    @a_column_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);

    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_maximum = maximum,
    @a_column_name = column_name
    from meta_small_int_columns
    where column_id = @a_column_id;
    
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add constraint x' + @a_column_id_str
    + ' check(' + @a_column_name
    + ' <= ' + @a_maximum + ')');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_small_int_maximum_check';

go

--Function of creating small int maximum check
create procedure p_drop_small_int_maximum_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Table name
    @a_table_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);

    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    exec ('alter table tbl_' 
    + @a_table_name
    + ' drop constraint x' + @a_column_id_str);
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_small_int_minimum';

go

--Function of setting small int column minimum
create procedure meta_set_small_int_minimum(
    --Column id
    @a_column_id binary(16),
    --Minimum
    @a_minimum numeric(5))
with execute as owner
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric(1),
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Old minimum
    @a_old_minimum numeric(5),
    --Column id str
    @a_column_id_str varchar(32);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_small_int_columns', 'small int', @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_id = table_id,
    @a_column_name = column_name,
    @a_old_minimum = minimum
    from meta_small_int_columns
    where column_id = @a_column_id;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Updating the metadata
    if @a_minimum is not null
        exec ('update meta_small_int_columns'
        + ' set minimum = ' + @a_minimum
        + ' where column_id = 0x' + @a_column_id_str);
    else
        exec ('update meta_small_int_columns'
        + ' set minimum = null'
        + ' where column_id = 0x' + @a_column_id_str);
     
    --There is no need to drop table view
     
    if @a_old_minimum is not null
        --Drop old constraint 
        exec dbo.p_drop_small_int_minimum_check @a_column_id;

    --Create new check
    if @a_minimum is not null
        exec dbo.p_create_small_int_minimum_check @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_small_int_minimum_check';

go

--Function of creating small int minimum check
create procedure p_create_small_int_minimum_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Table name
    @a_table_name varchar(26),
    --Minimum
    @a_minimum numeric(5),
    --Column name
    @a_column_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);

    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_minimum = minimum,
    @a_column_name = column_name
    from meta_small_int_columns
    where column_id = @a_column_id;
    
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add constraint n' + @a_column_id_str
    + ' check(' + @a_column_name
    + ' >= ' + @a_minimum + ')');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_small_int_minimum_check';

go

--Function of creating small int minimum check
create procedure p_drop_small_int_minimum_check(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Table name
    @a_table_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);

    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    exec ('alter table tbl_' 
    + @a_table_name
    + ' drop constraint n' + @a_column_id_str);
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_small_int_col_constrs';

go

--Function of creating all small int column constraints
create procedure p_create_small_int_col_constrs(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Minimum
    @a_minimum numeric(5),
    --Maximum
    @a_maximum numeric(5);
    
    select @a_minimum = minimum,
    @a_maximum = maximum
    from meta_small_int_columns
    where column_id = @a_column_id;
    
    if @a_minimum is not null
        exec dbo.p_create_small_int_minimum_check @a_column_id;
    if @a_maximum is not null
        exec dbo.p_create_small_int_maximum_check @a_column_id;
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_small_int_col_constrs';

go

--Function of dropping all small int column constraints
create procedure p_drop_small_int_col_constrs(
    --Column id
    @a_column_id binary(16))
as
begin
    declare
    --Minimum
    @a_minimum numeric(5),
    --Maximum
    @a_maximum numeric(5);
    
    select @a_minimum = minimum,
    @a_maximum = maximum
    from meta_small_int_columns
    where column_id = @a_column_id;
    
    if @a_minimum is not null
        exec dbo.p_drop_small_int_minimum_check @a_column_id;
        
    if @a_maximum is not null
        exec dbo.p_drop_small_int_maximum_check @a_column_id;
end;

--12----------------------------------------------------------------------
go

exec dbo.p_drop_proc_if_exists 'meta_add_guid_column';

go

--Function of adding new guid column
create procedure meta_add_guid_column(
    --Table id
    @a_table_id binary(16),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(max))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_not_exists @a_table_id, @a_column_id, @a_column_name, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;

    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Changing the metadata
    insert into meta_guid_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (@a_column_id, @a_table_id, @a_column_name,
        dbo.p_generate_order_index(@a_table_id), 1);
        
    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
         
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add ' + @a_column_name
    + ' binary(16)');
         
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;

    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_guid_default';

go

--Function of setting column default value
create procedure meta_set_guid_default(
    --Column id
    @a_column_id binary(16),
    --Default value
    @a_default_value binary(16))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Customization level
    @a_customization_level numeric(10),
    --Result
    @a_result numeric(1),
    --Default value str
    @a_default_value_str varchar(32),
    --Column id str
    @a_column_id_str varchar(32),
    --Old default value
    @a_old_default_value binary(16);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_guid_columns', 'guid', @a_result output;
    
    if @a_result = 0 return;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    set @a_default_value_str = dbo.p_bin_to_str(@a_default_value);
    
    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_customization_level = customization_level,
    @a_old_default_value = default_value
    from meta_guid_columns
    where column_id = @a_column_id;
    
    --If this is a system column
    if @a_customization_level = 0
    begin
        raiserror('Changing guid system column default value is forbidden (column_id = %s) (#error_code = 20008)',
        16, 1, @a_column_id_str);
        return;
    end;
    
    --If there is no need for change
    if (@a_old_default_value = @a_default_value) or
    (@a_old_default_value is null and @a_default_value is null)
        return;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    set xact_abort on;
    begin tran;
    
    --If there is an old default value
    if @a_old_default_value is not null
        --Dropping the old default value
        exec ('alter table tbl_' 
        + @a_table_name
        + ' drop constraint def_' + @a_column_id_str);
    
    --Updating the metadata
    update meta_guid_columns
    set default_value = @a_default_value
    where column_id = @a_column_id;
    
    --If there is a new default value
    if @a_default_value is not null
        exec ('alter table tbl_' 
        + @a_table_name
        + ' add constraint def_' + @a_column_id_str
        + ' default 0x' + @a_default_value_str + ' for ' + @a_column_name);        
    
    commit;
end;

--14----------------------------------------------------------------
go

exec dbo.p_drop_proc_if_exists 'meta_add_text_column';

go

--Function of adding new text column
create procedure meta_add_text_column(
    --Table id
    @a_table_id binary(16),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(max))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_not_exists @a_table_id, @a_column_id, @a_column_name, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;

    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Changing the metadata
    insert into meta_text_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (@a_column_id, @a_table_id, @a_column_name,
        dbo.p_generate_order_index(@a_table_id), 1);
        
    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
         
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add ' + @a_column_name
    + ' nvarchar(max)');
         
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;

    commit;
end;

--50-------------------------------------------------------------
go

exec dbo.p_drop_proc_if_exists 'meta_add_index';

go

--Function of adding new index
create procedure meta_add_index(
    --Index id
    @a_index_id binary(16),
    --Table id
    @a_table_id binary(16),
    --Is unique
    @a_is_unique numeric(1),
    --Column id list
    @a_column_id_list varbinary(256))
with execute as owner
as  
begin
	set nocount on;
	declare
	--Table name
    @a_table_name varchar(26),
    --Column list
    @a_column_list varchar(max),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(26),
    --Current index
    @a_curr_index int,
    --Current position
    @a_curr_position int,
    --Item length
    @a_item_length int,
    --Row count
    @a_row_count numeric,
    --Index id str
    @a_index_id_str varchar(32);
    
    set @a_curr_index = 1;
    set @a_curr_position = 1;
    set @a_item_length = 16;
    set @a_column_list = '';
    set @a_index_id_str = dbo.p_bin_to_str(@a_index_id);

    --If there is no column specified
    if @a_column_id_list is null
        return;

    --Searching for the index with the same id
    select @a_row_count = count(*)
    from meta_indexes
    where index_id = @a_index_id;
    
    if @a_row_count > 0
    begin
        raiserror('A index with the specified id was already added (index_id = %s) (#error_code = 20028)',
        16, 1, @a_index_id_str);
        return;
    end;
    
    --Searching for the index with the same column set
    select @a_row_count = count(*)
    from meta_indexes
    where column_id_list = @a_column_id_list;
    
    if @a_row_count > 0
    begin
        raiserror('A index with the specified column set was already added (#error_code = 20029)',
        16, 1);
        return;
    end;
    
    --Reading table name
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Reading user columns
    while datalength(@a_column_id_list) - @a_curr_position + 1 >= 16 
    begin
		
		set @a_column_id = substring(@a_column_id_list,
			@a_curr_position, @a_item_length);
		    
        set @a_curr_index = @a_curr_index + 1;
        set @a_curr_position = @a_curr_position + 16;
            
        --Reading column name
        select @a_column_name = column_name
        from meta_table_columns
        where table_id = @a_table_id
        and column_id = @a_column_id;
            
        if len(@a_column_list) > 0
            set @a_column_list = @a_column_list + ',';
            
        set @a_column_list = @a_column_list + @a_column_name;        
    end;
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    insert into meta_indexes(index_id, table_id, is_unique, column_id_list)
    values (@a_index_id, @a_table_id, @a_is_unique, @a_column_id_list);
    
    --Creating the index
    exec ('create index i'
        + @a_index_id_str
        + ' on tbl_'  + @a_table_name
        + ' (' + @a_column_list + ')');
    
    if @a_is_unique = 1
		exec dbo.p_recreate_index_triggers @a_index_id;
        
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_recreate_index_triggers';

go

--Function that recreates the index triggers
create procedure p_recreate_index_triggers(
    --Index id
    @a_index_id binary(16))
as
begin
	declare
	--Index uniquenss
	@a_is_unique numeric,
	--Table id
	@a_table_id binary(16),
	--Column id list
	@a_column_id_list varbinary(256),
	--Table name
    @a_table_name varchar(26),
    --Column list
    @a_column_list varchar(max),
    --Column condition list
    @a_column_condition varchar(max),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(26),
    --Current index
    @a_curr_index int,
    --Current position
    @a_curr_position int,
    --Item length
    @a_item_length int,
    --Row count
    @a_row_count numeric,
    --Index id str
    @a_index_id_str varchar(32);
    
    set @a_curr_index = 1;
    set @a_curr_position = 1;
    set @a_item_length = 16;
    set @a_column_list = 'update(is_active)';
    set @a_column_condition = '';
    set @a_index_id_str = dbo.p_bin_to_str(@a_index_id);
    
    --Reading index properties
    select @a_table_id = table_id,
		@a_is_unique = is_unique,
		@a_column_id_list = column_id_list 
    from meta_indexes
    where index_id = @a_index_id;
    
    --Reading table name
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --If the index is unique
    if @a_is_unique = 1
    begin
        --Reading user columns
		while datalength(@a_column_id_list) - @a_curr_position + 1 >= 16 
		begin
			
			set @a_column_id = substring(@a_column_id_list,
				@a_curr_position, @a_item_length);
			    
			set @a_curr_index = @a_curr_index + 1;
			set @a_curr_position = @a_curr_position + 16;
	            
			--Reading column name
			select @a_column_name = column_name
			from meta_table_columns
			where table_id = @a_table_id
			and column_id = @a_column_id;
	            
			if len(@a_column_list) > 0
				set @a_column_list = @a_column_list + ' or ';
			if len(@a_column_condition) > 0
				set @a_column_condition = @a_column_condition + ' and ';
	            
			set @a_column_list = @a_column_list + 'update(' + @a_column_name + ')';
			set @a_column_condition = @a_column_condition + 's.' 
            + @a_column_name + '=t.' + @a_column_name;        
		end;
		
		--Dropping the trigger
		exec ('if object_id(''q' + @a_index_id_str + ''',''TR'') is not null'
		+ ' drop trigger q' + @a_index_id_str + ';');
    
        --Creating the trigger that enforces uniqueness
        exec ('create trigger q' + @a_index_id_str
        + ' on tbl_' + @a_table_name
        + ' after insert, update'        
        + ' as begin'
        + '   declare'
        + '   @a_recycle_bin numeric,'
        + '   @a_row_count numeric;'
              --Checking the target columns
        + '   if not (' + @a_column_list + ') return;'       
              --Checking the recycle bin availability
        + '   select @a_recycle_bin = int_value from meta_options where option_name = ''RECYCLE_BIN'';'
              --If the recycle bin is enabled
        + '   if @a_recycle_bin = 1'
                   --Searching for another row with the same column values in active rows
        + '        select @a_row_count = max(c) from (select count(*) c from inserted i, tbl_'
        +          @a_table_name + ' s, tbl_' + @a_table_name + ' t'
        + '        where t.id=i.id and s.is_active = 1 and ' + @a_column_condition + ') r;'
		      --If the recycle bin is not enabled
        + '   else'
                   --Searching for another row with the same column values in all rows
        + '        select @a_row_count = max(c) from (select count(*) c from inserted i, tbl_'
        +          @a_table_name + ' s, tbl_' + @a_table_name + ' t'
        + '        where t.id=i.id and ' + @a_column_condition + ') r;'        
        + '   if @a_row_count > 1'
        + '   begin'
        + '     raiserror(''The operation results in breaking the column values uniqueness (#error_code = 20055)'', 16, 1);'
        + '     rollback;'
        + '   end;'
        + ' end;');
    end;
end;

go

exec dbo.p_drop_proc_if_exists 'p_recreate_col_index_triggers';

go

--Function that recreates the column index triggers
create procedure p_recreate_col_index_triggers(
    --Column id
    @a_column_id binary(16))
as
begin
	declare
	--Table id
    @a_table_id binary(16);

    --Reading column properties
    select @a_table_id = table_id
    from meta_table_columns
    where column_id = @a_column_id;
    
    --Index id
    declare @a_index_id binary(16),
    --Index cursor
    @a_index_cursor cursor;
    
    set @a_index_cursor = cursor fast_forward read_only for
    select index_id
    from meta_indexes
    where table_id = @a_table_id
    and is_unique = 1
    and dbo.p_column_is_indexed(index_id, @a_column_id) = 1;
    
    open @a_index_cursor;
    fetch next from @a_index_cursor into @a_index_id;
    
    while @@fetch_status = 0
    begin
		exec dbo.p_recreate_index_triggers @a_index_id;
		fetch next from @a_index_cursor into @a_index_id;
    end;
    
    close @a_index_cursor;
    deallocate @a_index_cursor;
end;

go

exec dbo.p_drop_func_if_exists 'p_column_is_indexed';

go

--Function of checking if column is indexed
create function p_column_is_indexed(
    --Index id
    @a_index_id binary(16),
    --Column id
    @a_column_id binary(16))
returns numeric
as
begin
	declare
	--Index column id list
    @a_column_id_list varbinary(max),
    --Position
    @a_position numeric,
    --Result
    @a_result numeric;
    
    --Reading column id list
    select @a_column_id_list = column_id_list
    from meta_indexes
    where index_id = @a_index_id;
    
    set @a_position = charindex(@a_column_id, @a_column_id_list);
    
    if @a_position = 0
        set @a_result = 0;
    else if (@a_position - 1) % 16 = 0
        set @a_result = 1;
    else
        set @a_result = 0;
        
    return @a_result;
end;

go

exec dbo.p_drop_proc_if_exists 'p_delete_column_indexes';

go

--Function of deleting all indexes that include the column
create procedure p_delete_column_indexes(
    --Column id
    @a_column_id binary(16),
    --Saving the index list
    @a_save_index_list numeric(1) = 0)
as  
begin
	declare
	--Table id
    @a_table_id binary(16);
    
    --Reading column properties
    select @a_table_id = table_id
    from meta_table_columns
    where column_id = @a_column_id;
    
    --If there is a need to save the index list
    if @a_save_index_list = 1
		select index_id, is_unique, column_id_list into ##column_indexes
		from meta_indexes
		where table_id = @a_table_id
		and dbo.p_column_is_indexed(index_id, @a_column_id) = 1;
    
    --Index id
    declare @a_index_id binary(16),
    --Index cursor
    @a_index_cursor cursor;
    
    set @a_index_cursor = cursor fast_forward read_only for
    select index_id
    from meta_indexes
    where table_id = @a_table_id
		and dbo.p_column_is_indexed(index_id, @a_column_id) = 1;
    
    open @a_index_cursor;
    fetch next from @a_index_cursor into @a_index_id;
    
    while @@fetch_status = 0
    begin
		exec dbo.meta_delete_index @a_index_id;
		fetch next from @a_index_cursor into @a_index_id;
    end;
    
    close @a_index_cursor;
    deallocate @a_index_cursor;    
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_index';

go

--Function of dropping table index
create procedure meta_delete_index(
    --Index id
    @a_index_id binary(16))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table id
	@a_table_id binary(16),
	--Table name
	@a_table_name varchar(26),
	--Row count
    @a_row_count numeric,
    --Index uniqueness
    @a_is_unique numeric,
    --Index id str
    @a_index_id_str varchar(32);
    
    set @a_index_id_str = dbo.p_bin_to_str(@a_index_id);
    
    --Searching for the index with the same id
    select @a_row_count = count(*)
    from meta_indexes
    where index_id = @a_index_id;
    
    if @a_row_count = 0
    begin
        raiserror('A index with the specified id was not found (index_id = %s) (#error_code = 20030)',
        16, 1, @a_index_id_str);
        return;
    end;
    
    --Reading table id
    select @a_table_id = table_id, @a_is_unique = is_unique
    from meta_indexes
    where index_id = @a_index_id;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    set xact_abort on;
    begin tran;

    --Updating the metadata
    delete from meta_indexes
    where index_id = @a_index_id;
    
    exec ('drop index tbl_' + @a_table_name + '.i'
    + @a_index_id_str);
    
    --If the index is unique
    if @a_is_unique = 1
        --Dropping the trigger
        exec ('drop trigger q' + @a_index_id_str);
    
    commit;
end;

--100--------------------------------------------------------
go

exec dbo.p_drop_proc_if_exists 'meta_add_unit';

go

--Function of adding a business unit
create procedure meta_add_unit(
    --Unit id
    @a_unit_id binary(16),
    --Unit name
    @a_unit_name nvarchar(26))
with execute as owner
as
begin
    set nocount on;
    declare
    --Unit id str
    @a_unit_id_str varchar(32);
    
    set @a_unit_id_str = dbo.p_bin_to_str(@a_unit_id);
    
    --Searching for the unit with the same id
    if exists(select 1 from meta_units where unit_id = @a_unit_id)
    begin
        raiserror('A unit with the specified id was already added (unit_id = %s) (#error_code = 20031)',
            16, 1, @a_unit_id_str);
        return;
    end;
    
    --Searching for the unit with the same name
    if exists(select 1 from meta_units where unit_name = @a_unit_name)
    begin
        raiserror('A unit with the specified name was already added (unit_name = %s) (#error_code = 20017)',
            16, 1, @a_unit_name);
        return;
    end;
    
    set xact_abort on;
    begin tran;

    --Updating the metadata
    insert into meta_units(unit_id, unit_name)
    values(@a_unit_id, @a_unit_name);
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_check_unit_exists';

go

--Function of checking unit existence
create procedure p_check_unit_exists(
    --Unit id
    @a_unit_id binary(16),
    --Result
    @a_result numeric(1) output)
as
begin
    declare
    --Unit id str
    @a_unit_id_str varchar(32);
    
    --Searching for the unit
    if not exists(select 1 from meta_units where unit_id = @a_unit_id)
    begin
        set @a_unit_id_str = dbo.p_bin_to_str(@a_unit_id);
        
        raiserror('A unit with the specified id was not found (unit_id = %s) (#error_code = 20032)',
        16, 1, @a_unit_id_str);
        set @a_result = 0;
        return;
    end;
    
    set @a_result = 1;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_unit';

go

--Function of deleting a business unit
create procedure meta_delete_unit(
    --Unit id
    @a_unit_id binary(16))
with execute as owner
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_unit_exists @a_unit_id, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    --Getting affected users
    select distinct user_name into #a_affected_users
    from meta_user_units
    --Getting affected unit users
    where unit_id in (select * from p_get_unit_parents_children(@a_unit_id));
    
    --References to and from unit will be deleted on cascade
    
    --Deleting the unit
    delete from meta_units
    where unit_id = @a_unit_id;
    
    declare
    --Affected user name
    @a_affected_user_name varchar(26),
    --Affected user cursor
    @a_affected_user_cursor cursor;
    
    --Refreshing users acl
    set @a_affected_user_cursor = cursor fast_forward read_only for
    select user_name from #a_affected_users;
    
    open @a_affected_user_cursor;
    fetch next from @a_affected_user_cursor into @a_affected_user_name;
    
    while @@fetch_status = 0
    begin
        exec dbo.p_refresh_user_acl @a_affected_user_name;    
        fetch next from @a_affected_user_cursor into @a_affected_user_name;
    end;
    
    close @a_affected_user_cursor;
    deallocate @a_affected_user_cursor;
    
    commit;
end;

go

exec dbo.p_drop_func_if_exists 'p_get_unit_parents';

go

--Function of getting unit parents
create function p_get_unit_parents(
    --Unit id
    @a_unit_id binary(16))
returns table as
return
( 
    with cte_unit_parents(unit_id) as
    (
        --The target unit
        select @a_unit_id
        union
        --The parent units
        select parent_unit_id from meta_unit_parents
        where unit_id = @a_unit_id
        union all
        select e.parent_unit_id
        from meta_unit_parents as e
            inner join cte_unit_parents as d
            on e.unit_id = d.unit_id		
    )
    select distinct unit_id
    from cte_unit_parents
);

go

exec dbo.p_drop_func_if_exists 'p_get_unit_children';

go

--Function of getting unit children
create function p_get_unit_children(
    --Unit id
    @a_unit_id binary(16))
returns table as
return
( 
    with cte_unit_parents(unit_id) as
    (
        --The target unit
        select @a_unit_id
        union
        --The child units
        select unit_id from meta_unit_parents
        where parent_unit_id = @a_unit_id
        union all
        select e.unit_id
        from meta_unit_parents as e
            inner join cte_unit_parents as d
            on e.parent_unit_id = d.unit_id		
    )
    select distinct unit_id
    from cte_unit_parents
);

go

exec dbo.p_drop_func_if_exists 'p_get_unit_parents_children';

go

--Function of getting unit children and parents
create function p_get_unit_parents_children(
    --Unit id
    @a_unit_id binary(16))
returns table as
return
( 
    with cte_unit_parents(unit_id) as
    (
        --The child units
        select unit_id from p_get_unit_children(@a_unit_id)
        union
        --The unit parents
        select unit_id from p_get_unit_parents(@a_unit_id)
    )
    select distinct unit_id
    from cte_unit_parents
);

go

exec dbo.p_drop_proc_if_exists 'meta_add_unit_parent';

go

--Function of adding unit parent
create procedure meta_add_unit_parent(
    --Unit id
    @a_unit_id binary(16),
    --Parent unit id
    @a_parent_unit_id binary(16))
with execute as owner
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric(1),
    --Unit id str
    @a_unit_id_str varchar(32),
    --Parent unit id str
    @a_parent_unit_id_str varchar(32);
    --Unit id list
    declare @a_unit_id_list table (unit_id binary(16));
    
    exec dbo.p_check_unit_exists @a_unit_id, @a_result output;
    
    if @a_result = 0 return;
    
    exec dbo.p_check_unit_exists @a_parent_unit_id, @a_result output;
    
    if @a_result = 0 return;
    
    set @a_unit_id_str = dbo.p_bin_to_str(@a_unit_id);
    set @a_parent_unit_id_str = dbo.p_bin_to_str(@a_parent_unit_id);
    
    --Searching for the same unit parent
    if exists(select 1 from meta_unit_parents
        where unit_id = @a_unit_id
        and parent_unit_id = @a_parent_unit_id)
    begin
        raiserror('Specified parent unit was already added (unit_id = %s, parent_unit_id = %s) (#error_code = 20050)',
        16, 1, @a_unit_id_str, @a_parent_unit_id_str);
        return;
    end;
           
    --Checking that there will be no cycle
    if exists(select 1 from dbo.p_get_unit_parents(@a_parent_unit_id)
        where unit_id = @a_unit_id)
    begin
        raiserror('Building a cycle of units is forbidden (#error_code = 20033)', 16, 1);
        return;
    end;
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    insert into meta_unit_parents (unit_id, parent_unit_id)
    values (@a_unit_id, @a_parent_unit_id);
    
    declare
    --Affected user name
    @a_affected_user_name varchar(26),
    --Affected user cursor
    @a_affected_user_cursor cursor;
    
    --Getting affected new users
    set @a_affected_user_cursor = cursor fast_forward read_only for
    select distinct user_name
        from meta_user_units
        --Getting affected unit users
        where unit_id in (select * from p_get_unit_parents_children(@a_unit_id));
    
    --Refreshing new users acl
    open @a_affected_user_cursor;
    fetch next from @a_affected_user_cursor into @a_affected_user_name;
    
    while @@fetch_status = 0
    begin
        exec dbo.p_refresh_user_acl @a_affected_user_name;		
        fetch next from @a_affected_user_cursor into @a_affected_user_name;
    end;
    
    close @a_affected_user_cursor;
    deallocate @a_affected_user_cursor;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_unit_parent';

go

--Function of deleting unit parent
create procedure meta_delete_unit_parent(
    --Unit id
    @a_unit_id binary(16),
    --Parent unit id
    @a_parent_unit_id binary(16))
with execute as owner
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric,
    --Unit id str
    @a_unit_id_str varchar(32),
    --Parent unit id str
    @a_parent_unit_id_str varchar(32);
    
    exec dbo.p_check_unit_exists @a_unit_id, @a_result output;
    
    if @a_result = 0 return;
    
    exec dbo.p_check_unit_exists @a_parent_unit_id, @a_result output;
    
    if @a_result = 0 return;
    
    set @a_unit_id_str = dbo.p_bin_to_str(@a_unit_id);
    set @a_parent_unit_id_str = dbo.p_bin_to_str(@a_parent_unit_id);
    
    --Searching for the unit parent
    if not exists(select 1 from meta_unit_parents
        where unit_id = @a_unit_id
        and parent_unit_id = @a_parent_unit_id)
    begin
        raiserror('Specified parent unit was not found (unit_id = %s, parent_unit_id = %s) (#error_code = 20051)',
        16, 1, @a_unit_id_str, @a_parent_unit_id_str);
        return;
    end;
    
    --Getting affected old users
    select distinct user_name into #a_affected_users
    from meta_user_units
    --Getting affected unit users
    where unit_id in (select * from p_get_unit_parents_children(@a_unit_id));
    
    set xact_abort on;
    begin tran;
        
    --Updating the metadata
    delete from meta_unit_parents
    where unit_id = @a_unit_id
    and parent_unit_id = @a_parent_unit_id;
    
    declare
    --Affected user name
    @a_affected_user_name varchar(26),
    --Affected user cursor
    @a_affected_user_cursor cursor;
    
    --Refreshing old users acl
    set @a_affected_user_cursor = cursor fast_forward read_only for
    select user_name from #a_affected_users;
    
    open @a_affected_user_cursor;
    fetch next from @a_affected_user_cursor into @a_affected_user_name;
    
    while @@fetch_status = 0
    begin
        exec dbo.p_refresh_user_acl @a_affected_user_name;    
        fetch next from @a_affected_user_cursor into @a_affected_user_name;
    end;
    
    close @a_affected_user_cursor;
    deallocate @a_affected_user_cursor;
    
    commit;
end;

go

--101-------------------------------------------------------------
go

exec dbo.p_drop_proc_if_exists 'meta_add_role';

go

--Function of adding a role
create procedure meta_add_role(
    --Role name
    @a_role_name varchar(max))
with execute as owner
as
begin
	set nocount on;
	declare
	--Row count
    @a_row_count numeric;
    
    select @a_row_count = count(*)
    from meta_roles
    where role_name = lower(@a_role_name);
    
    if @a_row_count > 0
    begin
        raiserror('A role with the specified name was already added (role_name = %s) (#error_code = 20034)',
			16, 1, @a_role_name);
		return;
    end;
    
    set xact_abort on;
    begin tran;

	--If there is no role with that name
	if not exists (
		select * from sys.database_principals 
		WHERE name = @a_role_name and type = 'R')
		--Creating a role
		exec ('create role ' + @a_role_name);

    --Updating the metadata
    insert into meta_roles (role_name)
    values(lower(@a_role_name));
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_check_role_exists';

go

--Function of checking role existence
create procedure p_check_role_exists(
    --Role name
    @a_role_name varchar(26),
    --Result
    @a_result numeric(1) output)
as
begin
    declare
    --Row count
    @a_row_count numeric;
    
    select @a_row_count = count(*)
    from meta_roles
    where role_name = lower(@a_role_name);
    
    if @a_row_count = 0
    begin
        raiserror('A role with the specified name was not found (role_name = %s) (#error_code = 20035)',
        16, 1, @a_role_name);
        set @a_result = 0;
        return;
    end;
    
    set @a_result = 1;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_role';

go

--Function of deleting a role
create procedure meta_delete_role(
    --Role name
    @a_role_name varchar(max))
with execute as owner
as
begin
    set nocount on;
    declare
    --Row count
    @a_row_count numeric,
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_role_exists @a_role_name, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    --Getting affected users
    select user_name into #a_affected_users
    from dbo.p_users_affected_by_principal(@a_role_name);
    
    --Deleting user enrollments
    declare
    --Enrolled user name
    @a_enrolled_user_name varchar(26),
    --Enrolled user cursor
    @a_enrolled_user_cursor cursor;
    
    set @a_enrolled_user_cursor = cursor fast_forward read_only for
    select user_name from meta_user_roles
    where container_role_name = @a_role_name;
    
    open @a_enrolled_user_cursor;
    fetch next from @a_enrolled_user_cursor into @a_enrolled_user_name;
    
    while @@fetch_status = 0
    begin
        exec dbo.meta_delete_user_from_role @a_enrolled_user_name, @a_role_name;
        fetch next from @a_enrolled_user_cursor into @a_enrolled_user_name;
    end;
    
    close @a_enrolled_user_cursor;
    deallocate @a_enrolled_user_cursor;
    
    --Deleting role enrollments
    declare
    --Enrolled role name
    @a_enrolled_role_name varchar(26),
    --Enrolled role cursor
    @a_enrolled_role_cursor cursor;
    
    set @a_enrolled_role_cursor = cursor fast_forward read_only for
    select role_name from meta_role_roles
    where container_role_name = @a_role_name;
    
    open @a_enrolled_role_cursor;
    fetch next from @a_enrolled_role_cursor into @a_enrolled_role_name;
    
    while @@fetch_status = 0
    begin
        exec dbo.meta_delete_role_from_role @a_enrolled_role_name, @a_role_name;
        fetch next from @a_enrolled_role_cursor into @a_enrolled_role_name;
    end;
    
    close @a_enrolled_role_cursor;
    deallocate @a_enrolled_role_cursor;
    
    --Updating the metadata
    delete from meta_roles
    where role_name = lower(@a_role_name);
    
    --Deleting all role acl
    delete from meta_table_acl
    where principal_name = lower(@a_role_name);
    
    --Deleting a role    
    exec ('drop role ' + @a_role_name);
    
    declare
    --Affected user name
    @a_affected_user_name varchar(26),
    --Affected user cursor
    @a_affected_user_cursor cursor;
    
    --Updating role users acl
    set @a_affected_user_cursor = cursor fast_forward read_only for
    select user_name from #a_affected_users;
    
    open @a_affected_user_cursor;
    fetch next from @a_affected_user_cursor into @a_affected_user_name;
    
    while @@fetch_status = 0
    begin
        exec dbo.p_refresh_user_acl @a_affected_user_name;
        fetch next from @a_affected_user_cursor into @a_affected_user_name;
    end;
    
    close @a_affected_user_cursor;
    deallocate @a_affected_user_cursor;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_add_user_in_role';

go

--Function of adding user in role
create procedure meta_add_user_in_role(
    --User name
    @a_user_name varchar(max),
    --Role name
    @a_role_name varchar(max))
with execute as owner
as
begin
	set nocount on;
	declare
	--Row count
    @a_row_count numeric,
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_user_exists @a_user_name, @a_result output;
    
    if @a_result = 0 return;
    
    exec dbo.p_check_role_exists @a_role_name, @a_result output;
    
    if @a_result = 0 return;
    
    --Searching for user enrollment
    select @a_row_count = count(*)
    from meta_user_roles
    where user_name = lower(@a_user_name) 
    and container_role_name = lower(@a_role_name);
    
    if @a_row_count > 0
    begin
        raiserror('Adding a user in a role twice is forbidden (role_name = %s, user_name = %s) (#error_code = 20037)',
			16, 1, @a_role_name, @a_user_name);
        return;
    end;
    
    --Enrolling user in role
    declare @retstat int;
    exec @retstat = sp_addrolemember @a_role_name, @a_user_name;
    
    --If something went wrong
    if @retstat <> 0 return;
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    insert into meta_user_roles (user_name, container_role_name)
    values(lower(@a_user_name), lower(@a_role_name));
    
    --Refreshing user acl
    exec dbo.p_refresh_user_acl @a_user_name;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_user_from_role';

go

--Function of removing user from role
create procedure meta_delete_user_from_role(
    --User name
    @a_user_name varchar(max),
    --Role name
    @a_role_name varchar(max))
with execute as owner
as
begin
	set nocount on;
	declare
	--Row count
    @a_row_count numeric,
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_user_exists @a_user_name, @a_result output;
    
    if @a_result = 0 return;
    
    exec dbo.p_check_role_exists @a_role_name, @a_result output;
    
    if @a_result = 0 return;
    
    --Searching for user enrollment
    select @a_row_count = count(*)
    from meta_user_roles
    where user_name = lower(@a_user_name)
    and container_role_name = lower(@a_role_name);
    
    if @a_row_count = 0
    begin
        raiserror('Specified user enrollment was not found (role_name = %s, user_name = %s) (#error_code = 20039)',
        16, 1, @a_role_name, @a_user_name);
        return;
    end;
    
    set xact_abort on;
    begin tran;
    
    --Deleting user from role
    declare @retstat int;
    exec sp_droprolemember @a_role_name, @a_user_name;
    
    if @retstat <> 0 return; --If something went wrong

    --Updating the metadata
    delete from meta_user_roles
    where user_name = lower(@a_user_name) 
    and container_role_name = lower(@a_role_name);
    
    --Refreshing user acl
    exec dbo.p_refresh_user_acl @a_user_name;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_add_role_in_role';

go

--Function of adding role in role
create procedure meta_add_role_in_role(
    --Role name
    @a_role_name varchar(max),
    --Container role name
    @a_container_role_name varchar(max))
with execute as owner
as
begin
	set nocount on;
	declare
    --Row count
    @a_row_count numeric,
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_role_exists @a_container_role_name, @a_result output;
    
    if @a_result = 0 return;
    
    exec dbo.p_check_role_exists @a_role_name, @a_result output;
    
    if @a_result = 0 return;
    
    --Searching for user enrollment
    select @a_row_count = count(*)
    from meta_role_roles
    where role_name = lower(@a_role_name) 
    and container_role_name = lower(@a_container_role_name);
    
    if @a_row_count > 0
    begin
        raiserror('Adding a role in a role twice is forbidden (container_role_name = %s, role_name = %s) (#error_code = 20038)',
			16, 1, @a_container_role_name, @a_role_name);
		return;
    end;
    
    --Adding role in role
    declare @retstat int;
    exec @retstat = sp_addrolemember @a_container_role_name, @a_role_name;
	
	if @retstat <> 0 return; --If something went wrong
	
	set xact_abort on;
    begin tran;	
    
    --Updating the metadata
    insert into meta_role_roles(role_name, container_role_name)
    values(lower(@a_role_name), lower(@a_container_role_name));
    
    declare
    --Affected user name
    @a_affected_user_name varchar(26),
    --Affected user cursor
    @a_affected_user_cursor cursor;
    
    --Updating users acl
    set @a_affected_user_cursor = cursor fast_forward read_only for
    select user_name from dbo.p_users_affected_by_principal(@a_affected_user_name);
    
    open @a_affected_user_cursor;
    fetch next from @a_affected_user_cursor into @a_affected_user_name;
    
    while @@fetch_status = 0
    begin
		exec dbo.p_refresh_user_acl @a_affected_user_name;
		fetch next from @a_affected_user_cursor into @a_affected_user_name;
    end;
    
    close @a_affected_user_cursor;
    deallocate @a_affected_user_cursor;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_role_from_role';

go

--Function of removing role from role
create procedure meta_delete_role_from_role(
    --Role name
    @a_role_name varchar(max),
    --Container role name
    @a_container_role_name varchar(max))
with execute as owner
as
begin
	set nocount on;
	declare
	--Row count
    @a_row_count numeric,
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_role_exists @a_container_role_name, @a_result output;
    
    if @a_result = 0 return;
    
    exec dbo.p_check_role_exists @a_role_name, @a_result output;
    
    if @a_result = 0 return;
    
    --Searching for role enrollment
    select @a_row_count = count(*)
    from meta_role_roles
    where role_name = lower(@a_role_name)
    and container_role_name = lower(@a_container_role_name);
    
    if @a_row_count = 0
    begin
        raiserror('Specified role enrollment was not found (container_role_name = %s, role_name = %s) (#error_code = 20040)',
        16, 1, @a_container_role_name, @a_role_name);
        return;
    end;
    
    declare @retstat int;
    exec sp_droprolemember @a_container_role_name, @a_role_name;
    
    if @retstat <> 0 return; --If something went wrong
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    delete from meta_role_roles
    where  role_name = lower(@a_role_name)
    and container_role_name = lower(@a_container_role_name);
    
    declare
    --Affected user name
    @a_affected_user_name varchar(26),
    --Affected user cursor
    @a_affected_user_cursor cursor;
    
    --Updating users acl
    set @a_affected_user_cursor = cursor fast_forward read_only for
    select user_name from dbo.p_users_affected_by_principal(@a_role_name);
    
    open @a_affected_user_cursor;
    fetch next from @a_affected_user_cursor into @a_affected_user_name;
    
    while @@fetch_status = 0
    begin
		exec dbo.p_refresh_user_acl @a_affected_user_name;
		fetch next from @a_affected_user_cursor into @a_affected_user_name;
    end;
    
    close @a_affected_user_cursor;
    deallocate @a_affected_user_cursor;
	
	commit;
end;


--102----------------------------------------------------------------
go

exec dbo.p_drop_proc_if_exists 'meta_get_current_user';

go

--Function of getting current user
create procedure meta_get_current_user(
    @a_current_user_name varchar(30) output)
with execute as caller
as
begin
    set nocount on;
    select @a_current_user_name = original_login();
end;

go

exec dbo.p_drop_proc_if_exists 'p_check_user_exists';

go

--Function of checking user existence
create procedure p_check_user_exists(
    --User name
    @a_user_name varchar(26),
    --Result
    @a_result numeric(1) output)
as
begin
    declare
    --Row count
    @a_row_count numeric;
    
    select @a_row_count = count(*) 
    from meta_users
    where user_name = lower(@a_user_name);
    
    if @a_row_count = 0
    begin
         raiserror('A user with the specified name was not found (user_name = %s) (#error_code = 20036)',
         16, 1, @a_user_name);
         set @a_result = 0;
         return;
    end;
    
    set @a_result = 1;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_add_user';

go

--Function of adding new user
create procedure meta_add_user(
    --User name
    @a_user_name varchar(max),
    --User password
    @a_user_password varchar(max))
with execute as self
as
begin
    set nocount on;
    declare
    --Row count
    @a_row_count numeric;
    
    set @a_user_name = lower(@a_user_name);
    
    --Searching for a user with the same name
    select @a_row_count = count(*)
    from meta_users
    where user_name = @a_user_name;
    
    if @a_row_count > 0
    begin
        raiserror('A user with the specified name was already added (user_name = %s) (#error_code = 20041)',
        16, 1, @a_user_name);
        return;
    end;
    
    set xact_abort on;
    
    --If there is no user with the specified name
    if not exists(select 1 from sys.server_principals
		where lower(name) = @a_user_name)
	begin
		--If the user password was specified
        if @a_user_password is not null 
			and len(rtrim(ltrim((@a_user_password)))) > 0
			--Creating a new login (this operation cannot be inside a transaction)
			exec ('create login ' + @a_user_name
			+ ' with password = ''' + @a_user_password
			+ ''' , CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF' );
		--If the user password was not specified
		else
			--Creating a new login with the default password
			exec ('create login ' + @a_user_name
			+ ' with password = ''P@ssw0rd'' , CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF' );
	end;
	--If there is a user with the specified name and the new password was specified
	else if @a_user_password is not null 
			and len(rtrim(ltrim((@a_user_password)))) > 0
		--Altering the user password
		exec ('alter login ' + @a_user_name
		+ ' with password = ''' + @a_user_password + '''');

    --Creating a new user (this operation cannot be inside a transaction)
    exec ('create user ' + @a_user_name
    + ' for login ' + @a_user_name
    + ' with DEFAULT_SCHEMA = dbo');
    
    --If there is no db user with the specified name
    if not exists(select * from sysusers where name=@a_user_name)
		exec ('create user ' + @a_user_name
		+ ' for login ' + @a_user_name
		+ ' with DEFAULT_SCHEMA = dbo');
	--If there is a db user with the specified name
	else
		exec ('alter user ' + @a_user_name 
		+ ' with login = ' + @a_user_name);
    
    begin tran;

    --Updating the metadata
    insert into meta_users(user_name)
    values(@a_user_name);
    
    --Granting default priviliges
    
    --Read metadata version
    exec ('grant select on meta_version to ' + @a_user_name);
    
    --Read and write dbms features
    exec ('grant select on meta_dbms_features to ' + @a_user_name);
    exec ('grant update on meta_dbms_features to ' + @a_user_name);
    
    --Read schema metadata
    exec ('grant select on meta_tables to ' + @a_user_name);
    exec ('grant select on meta_string_columns to ' + @a_user_name);
    exec ('grant select on meta_ref_columns to ' + @a_user_name);
    exec ('grant select on meta_ref_targets to ' + @a_user_name);
    exec ('grant select on meta_enum_columns to ' + @a_user_name);
    exec ('grant select on meta_enum_members to ' + @a_user_name);
    exec ('grant select on meta_big_int_columns to ' + @a_user_name);
    exec ('grant select on meta_binary_columns to ' + @a_user_name);
    exec ('grant select on meta_bool_columns to ' + @a_user_name);
    exec ('grant select on meta_datetime_columns to ' + @a_user_name);
    exec ('grant select on meta_decimal_columns to ' + @a_user_name);
    exec ('grant select on meta_guid_columns to ' + @a_user_name);
    exec ('grant select on meta_int_columns to ' + @a_user_name);
    exec ('grant select on meta_small_int_columns to ' + @a_user_name);
    exec ('grant select on meta_text_columns to ' + @a_user_name);
    exec ('grant select on meta_table_columns to ' + @a_user_name);
    
    --Reading indexes
    exec ('grant select on meta_indexes to ' + @a_user_name);
    
    --Reading options
    exec ('grant select on meta_options to ' + @a_user_name);
    
    --Executing common functions
    exec ('grant execute on meta_get_dbms_name to ' + @a_user_name);
    exec ('grant execute on meta_get_dbms_version to ' + @a_user_name);
    exec ('grant execute on meta_get_current_db_name to ' + @a_user_name);
    exec ('grant execute on meta_get_current_user to ' + @a_user_name);
    exec ('grant execute on meta_set_curr_user_password to ' + @a_user_name);
    
    --Reading user names and display names
    exec ('grant select on meta_user_names to ' + @a_user_name);
    
    --Current user properties
    exec ('grant select on meta_current_user to ' + @a_user_name); 
    exec ('grant update(display_name) on meta_current_user to ' + @a_user_name); 
    
    --Read unit list
    exec ('grant select on meta_units to ' + @a_user_name);
    exec ('grant select on meta_unit_parents to ' + @a_user_name);
    exec ('grant select on meta_user_units to ' + @a_user_name);

    --Read user's acl unit-level access rights
    exec ('grant select on meta_user_acl_unit_access to ' + @a_user_name);
    --Read user's acl full access rights
    exec ('grant select on meta_user_acl_full_access to ' + @a_user_name);
    --Read user's acl own rows access rights
    exec ('grant select on meta_user_acl_own_rows to ' + @a_user_name);
    
    --Soft deleting table rows
    exec ('grant execute on meta_soft_delete_table_row to ' + @a_user_name);
    
    --Calling main security function (security check is implemented inside this function)
    exec ('grant execute on meta_set_user_change_security to ' + @a_user_name);
    
    --Granting login permission
    exec dbo.meta_set_user_login @a_user_name, 1;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_user_login';

go

--Function of allowing user login
create procedure meta_set_user_login(
    --User name
    @a_user_name varchar(max),
    --Can login
    @a_can_login numeric(1))
with execute as owner
as
begin
    set nocount on;
    declare
    --Old CanLogin
    @a_old_can_login numeric(1),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_user_exists @a_user_name, @a_result output;
    
    if @a_result = 0 return;
    
    --If it is current user
    if lower(@a_user_name) = lower(original_login())
    begin
        raiserror('Changing current user login permission is forbidden (user_name = %s) (#error_code = 20042)',
            16, 1, @a_user_name);
        return;
    end;

    --Reading old CanLogin
    select @a_old_can_login = can_login
    from meta_users
    where user_name = lower(@a_user_name);
    
    --if there is no need for change
    if @a_can_login = @a_old_can_login
        return;
        
    set xact_abort on;
    begin tran;
    
    --Changing the metadata
    update meta_users
    set can_login = @a_can_login
    where user_name = lower(@a_user_name);
    
    --Granting or revoking the privilege
    if @a_can_login = 1
        exec ('grant connect to ' + @a_user_name);
    else
        exec ('revoke connect from ' + @a_user_name);
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_user_change_schema';

go

--Function of setting user CanChangeSchema
create procedure meta_set_user_change_schema(
    --User name
    @a_user_name varchar(max),
    --Can change schema
    @a_can_change_schema numeric(1))
with execute as owner
as
begin
    set nocount on;
    declare
    --Current user can change security
    @a_user_can_change_security numeric(1),
    --Old CanChangeSchema
    @a_old_can_change_schema numeric(1),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_user_exists @a_user_name, @a_result output;
    
    if @a_result = 0 return;
    
    --Reading old CanChangeSchema
    select @a_old_can_change_schema = can_change_schema
    from meta_users
    where user_name = lower(@a_user_name);
    
    --If there is no need for change
    if @a_can_change_schema = @a_old_can_change_schema
        return;
        
    set xact_abort on;
    begin tran;
    
    if @a_can_change_schema = 1
        exec dbo.p_enable_schema_change @a_user_name;
    else
        exec dbo.p_disable_schema_change @a_user_name;
    
    --Changing the metadata
    update meta_users
    set can_change_schema = @a_can_change_schema
    where user_name = lower(@a_user_name);
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_enable_schema_change';

go

--Function of enabling schema change
create procedure p_enable_schema_change(
    --User name
    @a_user_name varchar(26))
as
begin
    --Update metadata version
    exec ('grant update on meta_version to ' + @a_user_name);
    
    --Granting update on meta tables
    exec ('grant update(display_name, description) on meta_tables to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_string_columns to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_ref_columns to ' + @a_user_name);
    exec ('grant update(cascade_delete) on meta_ref_targets to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_enum_columns to ' + @a_user_name);
    exec ('grant update(display_name) on meta_enum_members to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_big_int_columns to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_binary_columns to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_bool_columns to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_datetime_columns to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_decimal_columns to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_guid_columns to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_int_columns to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_small_int_columns to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_text_columns to ' + @a_user_name);
    
    --Grant execute on schema changing functions
    exec ('grant execute on meta_add_table to ' + @a_user_name);
    exec ('grant execute on meta_delete_table to ' + @a_user_name);
    exec ('grant execute on meta_rename_table to ' + @a_user_name);
    exec ('grant execute on meta_add_string_column to ' + @a_user_name);
    exec ('grant execute on meta_delete_column to ' + @a_user_name);
    exec ('grant execute on meta_set_string_default to ' + @a_user_name);
    exec ('grant execute on meta_set_column_nullability to ' + @a_user_name);
    exec ('grant execute on meta_set_string_max_length to ' + @a_user_name);
    exec ('grant execute on meta_rename_column to ' + @a_user_name);
    exec ('grant execute on meta_add_ref_column to ' + @a_user_name);
    exec ('grant execute on meta_set_ref_default to ' + @a_user_name);
    exec ('grant execute on meta_add_ref_target to ' + @a_user_name);
    exec ('grant execute on meta_delete_ref_target to ' + @a_user_name);
    exec ('grant execute on meta_set_ref_checking to ' + @a_user_name);    
    exec ('grant execute on meta_add_enum_column to ' + @a_user_name);
    exec ('grant execute on meta_add_enum_member to ' + @a_user_name);
    exec ('grant execute on meta_delete_enum_member to ' + @a_user_name);
    exec ('grant execute on meta_set_enum_member_value to ' + @a_user_name);
    exec ('grant execute on meta_set_enum_default to ' + @a_user_name);
    exec ('grant execute on meta_set_enum_value_checking to ' + @a_user_name);
    exec ('grant execute on meta_add_decimal_column to ' + @a_user_name);
    exec ('grant execute on meta_set_decimal_minimum to ' + @a_user_name);
    exec ('grant execute on meta_set_decimal_maximum to ' + @a_user_name);
    exec ('grant execute on meta_set_decimal_precision to ' + @a_user_name);
    exec ('grant execute on meta_set_decimal_scale to ' + @a_user_name);
    exec ('grant execute on meta_set_decimal_default to ' + @a_user_name);
    exec ('grant execute on meta_add_datetime_column to ' + @a_user_name);
    exec ('grant execute on meta_set_datetime_maximum to ' + @a_user_name);
    exec ('grant execute on meta_set_datetime_minimum to ' + @a_user_name);
    exec ('grant execute on meta_set_datetime_default to ' + @a_user_name);
    exec ('grant execute on meta_add_bool_column to ' + @a_user_name);
    exec ('grant execute on meta_set_bool_default to ' + @a_user_name);
    exec ('grant execute on meta_add_binary_column to ' + @a_user_name);
    exec ('grant execute on meta_add_int_column to ' + @a_user_name);
    exec ('grant execute on meta_set_int_default to ' + @a_user_name);
    exec ('grant execute on meta_set_int_maximum to ' + @a_user_name);
    exec ('grant execute on meta_set_int_minimum to ' + @a_user_name);
    exec ('grant execute on meta_add_big_int_column to ' + @a_user_name);
    exec ('grant execute on meta_set_big_int_default to ' + @a_user_name);
    exec ('grant execute on meta_set_big_int_maximum to ' + @a_user_name);
    exec ('grant execute on meta_set_big_int_minimum to ' + @a_user_name);
    exec ('grant execute on meta_add_small_int_column to ' + @a_user_name);
    exec ('grant execute on meta_set_small_int_default to ' + @a_user_name);
    exec ('grant execute on meta_set_small_int_maximum to ' + @a_user_name);
    exec ('grant execute on meta_set_small_int_minimum to ' + @a_user_name);
    exec ('grant execute on meta_add_guid_column to ' + @a_user_name);
    exec ('grant execute on meta_set_guid_default to ' + @a_user_name);
    exec ('grant execute on meta_add_text_column to ' + @a_user_name);
    exec ('grant execute on meta_add_index to ' + @a_user_name);
    exec ('grant execute on meta_delete_index to ' + @a_user_name);
    
    --Restoring table rows
    exec ('grant execute on meta_restore_table_row to ' + @a_user_name);    
    --Deleting table rows
    exec ('grant execute on meta_delete_table_row to ' + @a_user_name);
    
    --Enabling/disabling recycle bin
    exec ('grant execute on meta_set_recycle_bin to ' + @a_user_name);
    
    --Granting all rights to all user tables
    declare @a_table_name varchar(26),
    @a_table_cursor cursor;
    
    set @a_table_cursor = cursor fast_forward read_only for
    select table_name from meta_tables;
    
    open @a_table_cursor;
    fetch next from @a_table_cursor into @a_table_name;
    
    while @@fetch_status = 0
    begin
        exec ('grant select, insert, update, delete on tbl_'
         + @a_table_name + ' to ' + @a_user_name);
        fetch next from @a_table_cursor into @a_table_name;
    end;
    
    close @a_table_cursor;
    deallocate @a_table_cursor;
end;

go

exec dbo.p_drop_proc_if_exists 'p_disable_schema_change';

go

--Function of disabling schema change
create procedure p_disable_schema_change(
    --User name
    @a_user_name varchar(26))
as
begin
    --Revoking rights

    --Update metadata version
    exec ('revoke update on meta_version from ' + @a_user_name);
    
    --Revoking update on meta tables
    exec ('revoke update on meta_tables from ' + @a_user_name);
    exec ('revoke update on meta_string_columns from ' + @a_user_name);
    exec ('revoke update on meta_ref_columns from ' + @a_user_name);
    exec ('revoke update on meta_ref_targets from ' + @a_user_name);
    exec ('revoke update on meta_enum_columns from ' + @a_user_name);
    exec ('revoke update on meta_enum_members from ' + @a_user_name);
    exec ('revoke update on meta_big_int_columns from ' + @a_user_name);
    exec ('revoke update on meta_binary_columns from ' + @a_user_name);
    exec ('revoke update on meta_bool_columns from ' + @a_user_name);
    exec ('revoke update on meta_datetime_columns from ' + @a_user_name);
    exec ('revoke update on meta_decimal_columns from ' + @a_user_name);
    exec ('revoke update on meta_guid_columns from ' + @a_user_name);
    exec ('revoke update on meta_int_columns from ' + @a_user_name);
    exec ('revoke update on meta_small_int_columns from ' + @a_user_name);
    exec ('revoke update on meta_text_columns from ' + @a_user_name);
    
    --Grant execute on schema changing functions
    exec ('revoke execute on meta_add_table from ' + @a_user_name);
    exec ('revoke execute on meta_delete_table from ' + @a_user_name);
    exec ('revoke execute on meta_rename_table from ' + @a_user_name);
    exec ('revoke execute on meta_add_string_column from ' + @a_user_name);
    exec ('revoke execute on meta_delete_column from ' + @a_user_name);
    exec ('revoke execute on meta_set_string_default from ' + @a_user_name);
    exec ('revoke execute on meta_set_column_nullability from ' + @a_user_name);
    exec ('revoke execute on meta_set_string_max_length from ' + @a_user_name);
    exec ('revoke execute on meta_rename_column from ' + @a_user_name);
    exec ('revoke execute on meta_add_ref_column from ' + @a_user_name);
    exec ('revoke execute on meta_set_ref_default from ' + @a_user_name);
    exec ('revoke execute on meta_add_ref_target from ' + @a_user_name);
    exec ('revoke execute on meta_delete_ref_target from ' + @a_user_name);
    exec ('revoke execute on meta_set_ref_checking from ' + @a_user_name);
    exec ('revoke execute on meta_add_enum_column from ' + @a_user_name);
    exec ('revoke execute on meta_add_enum_member from ' + @a_user_name);
    exec ('revoke execute on meta_delete_enum_member from ' + @a_user_name);
    exec ('revoke execute on meta_set_enum_member_value from ' + @a_user_name);
    exec ('revoke execute on meta_set_enum_default from ' + @a_user_name);
    exec ('revoke execute on meta_set_enum_value_checking from ' + @a_user_name);
    exec ('revoke execute on meta_add_decimal_column from ' + @a_user_name);
    exec ('revoke execute on meta_set_decimal_minimum from ' + @a_user_name);
    exec ('revoke execute on meta_set_decimal_maximum from ' + @a_user_name);
    exec ('revoke execute on meta_set_decimal_precision from ' + @a_user_name);
    exec ('revoke execute on meta_set_decimal_scale from ' + @a_user_name);
    exec ('revoke execute on meta_set_decimal_default from ' + @a_user_name);
    exec ('revoke execute on meta_add_datetime_column from ' + @a_user_name);
    exec ('revoke execute on meta_set_datetime_maximum from ' + @a_user_name);
    exec ('revoke execute on meta_set_datetime_minimum from ' + @a_user_name);
    exec ('revoke execute on meta_set_datetime_default from ' + @a_user_name);
    exec ('revoke execute on meta_add_bool_column from ' + @a_user_name);
    exec ('revoke execute on meta_set_bool_default from ' + @a_user_name);
    exec ('revoke execute on meta_add_binary_column from ' + @a_user_name);
    exec ('revoke execute on meta_add_int_column from ' + @a_user_name);
    exec ('revoke execute on meta_set_int_default from ' + @a_user_name);
    exec ('revoke execute on meta_set_int_maximum from ' + @a_user_name);
    exec ('revoke execute on meta_set_int_minimum from ' + @a_user_name);
    exec ('revoke execute on meta_add_big_int_column from ' + @a_user_name);
    exec ('revoke execute on meta_set_big_int_default from ' + @a_user_name);
    exec ('revoke execute on meta_set_big_int_maximum from ' + @a_user_name);
    exec ('revoke execute on meta_set_big_int_minimum from ' + @a_user_name);
    exec ('revoke execute on meta_add_small_int_column from ' + @a_user_name);
    exec ('revoke execute on meta_set_small_int_default from ' + @a_user_name);
    exec ('revoke execute on meta_set_small_int_maximum from ' + @a_user_name);
    exec ('revoke execute on meta_set_small_int_minimum from ' + @a_user_name);
    exec ('revoke execute on meta_add_guid_column from ' + @a_user_name);
    exec ('revoke execute on meta_set_guid_default from ' + @a_user_name);
    exec ('revoke execute on meta_add_text_column from ' + @a_user_name);
    exec ('revoke execute on meta_add_index from ' + @a_user_name);
    exec ('revoke execute on meta_delete_index from ' + @a_user_name);
    
    --Restoring table rows
    exec ('revoke execute on meta_restore_table_row from ' + @a_user_name);    
    --Deleting table rows
    exec ('revoke execute on meta_delete_table_row from ' + @a_user_name);
    
    --Enabling/disabling recycle bin
    exec ('revoke execute on meta_set_recycle_bin from ' + @a_user_name);
    
    --Revoking all rights from all user tables
    declare @a_table_name varchar(26),
    @a_table_cursor cursor;
    
    set @a_table_cursor = cursor fast_forward read_only for
    select table_name from meta_tables;
    
    open @a_table_cursor;
    fetch next from @a_table_cursor into @a_table_name;
    
    while @@fetch_status = 0
    begin
        exec ('revoke select, insert, update, delete on tbl_'
         + @a_table_name + ' from ' + @a_user_name);
        fetch next from @a_table_cursor into @a_table_name;
    end;
    
    close @a_table_cursor;
    deallocate @a_table_cursor;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_user_change_security';

go

--Function of setting user's ability to change security
create procedure meta_set_user_change_security(
    --User name
    @a_user_name varchar(max),
    --Can change security
    @a_can_change_security numeric(1))
with execute as owner
as
begin
    set nocount on;
    declare
    --Old CanChangeSecurity
    @a_old_can_change_security numeric(1),
    --Current user CanChangeSecurity
    @a_curr_user_change_security numeric(1),
    --Row count
    @a_row_count numeric,
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_user_exists @a_user_name, @a_result output;
    
    if @a_result = 0 return;
    
    --If it is current user
    if lower(@a_user_name) = lower(original_login())
    begin
        raiserror('Changing current user security change permission is forbidden (user_name = %s) (#error_code = 20043)',
        16, 1, @a_user_name);
        return;
    end;
    
    --Searching for current user
    select @a_row_count = count(*)
    from meta_users
    where user_name = lower(original_login());
    
    --If current user was found
    if @a_row_count <> 0
    begin
        --Reading current user ability to change security
        select @a_curr_user_change_security = can_change_security
        from meta_users
        where user_name = lower(original_login());
        
        if @a_curr_user_change_security = 0
        begin
            raiserror('Insufficient privileges to change another user security permissions (user_name = %s) (#error_code = 20045)',
                16, 1, @a_user_name);
            return;
        end;
    end;

    --Reading old CanChangeSecurity
    select @a_old_can_change_security = can_change_security
    from meta_users
    where user_name = lower(@a_user_name);
    
    --If there is no need for change
    if @a_can_change_security = @a_old_can_change_security
        return;
        
    set xact_abort on;
    begin tran;
    
    if @a_can_change_security = 1
        exec dbo.p_enable_security_change @a_user_name;
    else
        exec dbo.p_disable_security_change @a_user_name;
    
     --Changing the metadata
    update meta_users
    set can_change_security = @a_can_change_security
    where user_name = lower(@a_user_name);
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_enable_security_change';

go

--Function of enabling security change
create procedure p_enable_security_change(
    --User name
    @a_user_name varchar(26))
as
begin
    --Granting read on security metadata
    exec ('grant select on meta_users to ' + @a_user_name);
    exec ('grant select on meta_user_roles to ' + @a_user_name);
    exec ('grant select on meta_role_roles to ' + @a_user_name);
    exec ('grant select on meta_roles to ' + @a_user_name);
    exec ('grant select on meta_principals to ' + @a_user_name);
    exec ('grant select on meta_table_acl to ' + @a_user_name);
    exec ('grant select on meta_table_acl_unit_access to ' + @a_user_name);
    exec ('grant select on meta_table_acl_full_access to ' + @a_user_name);
    exec ('grant select on meta_table_acl_own_rows to ' + @a_user_name);
    
    --Granting update on security metadata
    exec ('grant update(display_name) on meta_users to ' + @a_user_name);
    exec ('grant update(unit_name, display_name) on meta_units to ' + @a_user_name);
    exec ('grant update(display_name) on meta_roles to ' + @a_user_name);
    
    --Granting execute on security admin functions
    exec ('grant execute on meta_add_unit to ' + @a_user_name);
    exec ('grant execute on meta_delete_unit to ' + @a_user_name);
    exec ('grant execute on meta_add_unit_parent to ' + @a_user_name);
    exec ('grant execute on meta_delete_unit_parent to ' + @a_user_name);
    exec ('grant execute on meta_add_role to ' + @a_user_name);
    exec ('grant execute on meta_delete_role to ' + @a_user_name);
    exec ('grant execute on meta_add_user to ' + @a_user_name);
    exec ('grant execute on meta_delete_user to ' + @a_user_name);
    exec ('grant execute on meta_set_user_login to ' + @a_user_name);
    exec ('grant execute on meta_set_user_change_schema to ' + @a_user_name);
    exec ('grant execute on meta_add_user_unit to ' + @a_user_name);
    exec ('grant execute on meta_delete_user_unit to ' + @a_user_name);
    exec ('grant execute on meta_add_user_in_role to ' + @a_user_name);
    exec ('grant execute on meta_delete_user_from_role to ' + @a_user_name);
    exec ('grant execute on meta_add_role_in_role to ' + @a_user_name);
    exec ('grant execute on meta_delete_role_from_role to ' + @a_user_name);
    exec ('grant execute on meta_add_table_rights to ' + @a_user_name);
    exec ('grant execute on meta_delete_table_rights to ' + @a_user_name);
    exec ('grant execute on meta_set_user_password to ' + @a_user_name);
    exec ('grant execute on meta_set_unit_security to ' + @a_user_name);
end;

go

exec dbo.p_drop_proc_if_exists 'p_disable_security_change';

go

--Function of disabling user ability to change security
create procedure p_disable_security_change(
    --User name
    @a_user_name varchar(26))
as
begin
    --Revoking read on security metadata
    exec ('revoke select on meta_users from ' + @a_user_name);
    exec ('revoke select on meta_user_roles from ' + @a_user_name);
    exec ('revoke select on meta_role_roles from ' + @a_user_name);
    exec ('revoke select on meta_roles from ' + @a_user_name);
    exec ('revoke select on meta_principals from ' + @a_user_name);
    exec ('revoke select on meta_table_acl from ' + @a_user_name);
    exec ('revoke select on meta_table_acl_unit_access from ' + @a_user_name);
    exec ('revoke select on meta_table_acl_full_access from ' + @a_user_name);
    exec ('revoke select on meta_table_acl_own_rows from ' + @a_user_name);
    
    --Revoking update on security metadata
    exec ('revoke update on meta_users from ' + @a_user_name);
    exec ('revoke update on meta_units from ' + @a_user_name);
    exec ('revoke update on meta_roles from ' + @a_user_name);
    
    --Revoking execute on security admin functions
    exec ('revoke execute on meta_add_unit from ' + @a_user_name);
    exec ('revoke execute on meta_delete_unit from ' + @a_user_name);
    exec ('revoke execute on meta_add_unit_parent from ' + @a_user_name);
    exec ('revoke execute on meta_delete_unit_parent from ' + @a_user_name);
    exec ('revoke execute on meta_add_role from ' + @a_user_name);
    exec ('revoke execute on meta_delete_role from ' + @a_user_name);
    exec ('revoke execute on meta_add_user from ' + @a_user_name);
    exec ('revoke execute on meta_delete_user from ' + @a_user_name);
    exec ('revoke execute on meta_set_user_login from ' + @a_user_name);
    exec ('revoke execute on meta_set_user_change_schema from ' + @a_user_name);
    exec ('revoke execute on meta_add_user_unit from ' + @a_user_name);
    exec ('revoke execute on meta_delete_user_unit from ' + @a_user_name);
    exec ('revoke execute on meta_add_user_in_role from ' + @a_user_name);
    exec ('revoke execute on meta_delete_user_from_role from ' + @a_user_name);
    exec ('revoke execute on meta_add_role_in_role from ' + @a_user_name);
    exec ('revoke execute on meta_delete_role_from_role from ' + @a_user_name);
    exec ('revoke execute on meta_add_table_rights from ' + @a_user_name);
    exec ('revoke execute on meta_delete_table_rights from ' + @a_user_name);
    exec ('revoke execute on meta_set_user_password from ' + @a_user_name);
    exec ('revoke execute on meta_set_unit_security from ' + @a_user_name);    
    
    --!!! No need to revoke execute from meta_set_user_change_security
    -- Otherwise there might be a deadlock
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_user';

go

--Function of deleting a user
create procedure meta_delete_user(
    --User name
    @a_user_name varchar(max))
with execute as self
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric(1);   
    
    exec dbo.p_check_user_exists @a_user_name, @a_result output;
    
    if @a_result = 0 return;
    
    --If it is current user
    if lower(@a_user_name) = lower(original_login())
    begin
        raiserror('Deleting current user is forbidden (user_name = %s) (#error_code = 20046)',
        16, 1, @a_user_name);
        return;
    end;
    
    set xact_abort on;
    begin tran;
    
    --Deleting the user
    exec ('drop user ' + @a_user_name);    
   
    --Updating the metadata
    delete from meta_users
    where user_name = lower(@a_user_name);
    
    --Deleting user acl
    delete from meta_table_acl
    where principal_name = lower(@a_user_name);
    
    commit;
end;

go

exec dbo.p_drop_func_if_exists 'p_get_user_principals';

go

--Function of getting user principals
create function p_get_user_principals(
    --User name
    @a_user_name varchar(26))
    returns table
as
return
(
    with cte_user_roles(role_name) as
    (
        select container_role_name
        from meta_user_roles
        where user_name = lower(@a_user_name)
        union all
        select e.container_role_name
        from meta_role_roles as e 
        inner join cte_user_roles as d on d.role_name = e.role_name
    )
    select distinct role_name as principal_name
    from cte_user_roles
    union
    select @a_user_name
);

go

exec dbo.p_drop_proc_if_exists 'meta_add_user_unit';

go

--Function of adding user's unit
create procedure meta_add_user_unit(
    --User name
    @a_user_name varchar(max),
    --Unit id
    @a_unit_id binary(16))
with execute as owner
as
begin
    set nocount on;
    declare
    --Unit id str
    @a_unit_id_str varchar(32),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_user_exists @a_user_name, @a_result output;
    
    if @a_result = 0 return;
    
    exec dbo.p_check_unit_exists @a_unit_id, @a_result output;
    
    if @a_result = 0 return;

    --Searching for the same user unit
    if exists(select 1 from meta_user_units
        where user_name = lower(@a_user_name)
        and unit_id = @a_unit_id)
    begin
        set @a_unit_id_str = dbo.p_bin_to_str(@a_unit_id);
        raiserror('Specified user unit was already added (unit_id = %s, user_name = %s) (#error_code = 20052)',
        16, 1, @a_unit_id_str, @a_user_name);
        return;
    end;
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    insert into meta_user_units (user_name, unit_id)
    values (@a_user_name, @a_unit_id);
    
    --Refreshing the user acl
    exec dbo.p_refresh_user_acl @a_user_name;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_user_unit';

go

--Function of deleting user's unit
create procedure meta_delete_user_unit(
    --User name
    @a_user_name varchar(max),
    --Unit id
    @a_unit_id binary(16))
with execute as owner
as
begin
    set nocount on;
    declare
    --Unit id str
    @a_unit_id_str varchar(32),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_user_exists @a_user_name, @a_result output;
    
    if @a_result = 0 return;
    
    exec dbo.p_check_unit_exists @a_unit_id, @a_result output;
    
    if @a_result = 0 return;

    --Searching for the user unit
    if not exists(select 1 from meta_user_units
        where user_name = lower(@a_user_name)
        and unit_id = @a_unit_id)
    begin
        set @a_unit_id_str = dbo.p_bin_to_str(@a_unit_id);
        raiserror('Specified user unit was not found (unit_id = %s, user_name = %s) (#error_code = 20053)',
        16, 1, @a_unit_id_str, @a_user_name);
        return;
    end;
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    delete from meta_user_units
    where user_name = lower(@a_user_name)
    and unit_id = @a_unit_id;
    
    --Refreshing the user acl
    exec dbo.p_refresh_user_acl @a_user_name;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_user_password';

go

--Function of changing user password
create procedure meta_set_user_password(
    --User name
    @a_user_name varchar(max),
    --New password
    @a_new_password varchar(max))
with execute as self
as
begin
    set nocount on;
    declare
    --Row count
    @a_row_count numeric,
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_user_exists @a_user_name, @a_result output;
    
    if @a_result = 0 return;

    set xact_abort on;
    begin tran;

    exec ('alter login ' + @a_user_name
    + ' with password = ''' + @a_new_password + '''');
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_curr_user_password';

go

--Function of changing current user password
create procedure meta_set_curr_user_password(
    --Old password
    @a_old_password varchar(max),
    --New password
    @a_new_password varchar(max))
with execute as self
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric(1),
    --Current user
    @a_curr_user varchar(255);
    
    set @a_curr_user = original_login();
    
    exec dbo.p_check_user_exists @a_curr_user, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;

    exec ('alter login ' + @a_curr_user
    + ' with password = ''' + @a_new_password
    + ''' old_password = ''' + @a_old_password + '''');
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_unit_security';

go

--Function of enabling/disabling table unit level security
create procedure meta_set_unit_security(
    --Table id
    @a_table_id binary(16),
    --Unit level security
    @a_unit_level_security numeric(1))
with execute as owner
as    
begin
    set nocount on;
    
    declare 
    --Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_table_exists @a_table_id, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;

    set @a_table_name = dbo.p_get_table_name(@a_table_id);

    --Updating the metadata
    update meta_tables
    set unit_level_security = @a_unit_level_security
    where table_id = @a_table_id;

    --Recreating table view
    exec p_drop_view_if_exists @a_table_name;

    exec p_create_table_view @a_table_id;
    
    commit;     
end;

--103--------------------------------------------------------------
go

exec dbo.p_drop_proc_if_exists 'p_check_principal_exists';

go

--Function of checking principal existence
create procedure p_check_principal_exists(
    --Principal name
    @a_principal_name varchar(26),
    --Result
    @a_result numeric(1) output)
as
begin
    --Searching for the principal
    if not exists(select 1 from meta_principals
        where principal_name = lower(@a_principal_name))
    begin
        raiserror('A principal with the specified name was not found (principal_name = %s) (#error_code = 20047)',
        16, 1, @a_principal_name);
        set @a_result = 0;
        return;
    end;
    
    set @a_result = 1;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_add_table_rights';

go

--Function of adding table access rights
create procedure meta_add_table_rights(
    --Table id
    @a_table_id binary(16),
    --Principal name
    @a_principal_name varchar(max),
    --Access type
    @a_access_type numeric(1),
    --Access level
    @a_access_level numeric(1))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_table_exists @a_table_id, @a_result output;
    
    if @a_result = 0 return;
    
    exec dbo.p_check_principal_exists @a_principal_name, @a_result output;
    
    if @a_result = 0 return;
    
    --Searching for the same table rights
    if exists(select 1 from meta_table_acl
        where table_id = @a_table_id
        and principal_name = @a_principal_name
        and access_type = @a_access_type)
    begin
        raiserror('A table permission with the same access type was already added (#error_code = 20048)',
        16, 1);
        return;
    end;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    insert into meta_table_acl(table_id, principal_name, access_type, access_level)
    values(@a_table_id, lower(@a_principal_name), @a_access_type, @a_access_level);
    
    if @a_access_type = 1
        exec ('grant select on ' + @a_table_name + ' to ' + @a_principal_name);
    else if @a_access_type = 2
        exec ('grant insert on ' + @a_table_name + ' to ' + @a_principal_name);
    else if @a_access_type = 3
        exec ('grant update on ' + @a_table_name + ' to ' + @a_principal_name);
    else if @a_access_type = 4
        exec ('grant delete on ' + @a_table_name + ' to ' + @a_principal_name);
    
    --Updating principal acl matrix
    exec dbo.p_update_principal_acl_matrix @a_table_id, @a_principal_name;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_update_principal_acl_matrix';

go

--Function of updating principal acl matrix
create procedure p_update_principal_acl_matrix(
    --Table id
    @a_table_id binary(16),
    --Principal name
    @a_principal_name varchar(26))
as
begin
    --If there is no acl for this principal
    if not exists(select 1 from meta_table_acl
        where table_id = @a_table_id
        and principal_name = lower(@a_principal_name))
        return;

    declare
    --Affected user name
    @a_affected_user_name varchar(26),
    --Affected user cursor
    @a_affected_user_cursor cursor;
    
    set @a_affected_user_cursor = cursor fast_forward read_only for
    select user_name
    from dbo.p_users_affected_by_principal(@a_principal_name);

    open @a_affected_user_cursor;
    fetch next from @a_affected_user_cursor into @a_affected_user_name;
    
    while @@fetch_status = 0
    begin
        exec dbo.p_update_user_acl_matrix @a_table_id, @a_affected_user_name, @a_principal_name;
        
        fetch next from @a_affected_user_cursor into @a_affected_user_name;
    end;
    
    close @a_affected_user_cursor;
    deallocate @a_affected_user_cursor;
end;

go

exec dbo.p_drop_proc_if_exists 'p_update_user_acl_matrix';

go

--Function of updating user acl matrix for the specified principal
create procedure p_update_user_acl_matrix(
    --Table id
    @a_table_id binary(16),
    --User name
    @a_user_name varchar(26),
    --Principal name
    @a_principal_name varchar(26))
as
begin
    declare
    --Access type
    @a_access_type numeric(1),
    --Access level
    @a_access_level numeric(1),
    --Principal acl cursor
    @a_principal_acl_cursor cursor;
    
    --Reading principal acl
    set @a_principal_acl_cursor = cursor fast_forward read_only for
    select access_type, access_level
    from meta_table_acl
    where table_id = @a_table_id
    and principal_name = lower(@a_principal_name);
    
    open @a_principal_acl_cursor;
    fetch next from @a_principal_acl_cursor into @a_access_type, @a_access_level; 
        
    while @@fetch_status = 0
    begin
        --If it is a full access right
        if @a_access_level = 6
            exec dbo.p_add_full_access_right @a_table_id, @a_user_name, @a_access_type;
        --If it is a own rows access rights
        else if @a_access_level = 1
            exec dbo.p_add_own_rows_access_right @a_table_id, @a_user_name, @a_access_type;
        --If it unit-level access rights
        else
        begin
            declare
            --Affected unit id
            @a_affected_unit_id binary(16),
            --Unit cursor
            @a_unit_cursor cursor;
            
            --Searching for the unit ids for the specified access type
            set @a_unit_cursor = cursor fast_forward read_only for
            select unit_id from dbo.p_get_unit_ids_for_unit_right(@a_user_name, @a_access_level);
            
            open @a_unit_cursor;
            fetch next from @a_unit_cursor into @a_affected_unit_id;
            
            while @@fetch_status = 0
            begin
                exec dbo.p_add_unit_access_right @a_table_id, @a_user_name, @a_affected_unit_id, @a_access_type;
                fetch next from @a_unit_cursor into @a_affected_unit_id;
            end;
            
            close @a_unit_cursor;
            deallocate @a_unit_cursor;
        end;
        
        fetch next from @a_principal_acl_cursor into @a_access_type, @a_access_level;       
    end;
    
    close @a_principal_acl_cursor;
    deallocate @a_principal_acl_cursor;
end;

go

exec dbo.p_drop_proc_if_exists 'p_add_unit_access_right';

go

--Function of adding a unit-level access right
create procedure p_add_unit_access_right(
    --Table id
    @a_table_id binary(16),
    --User name
    @a_user_name varchar(26),
    --The unit id
    @a_unit_id binary(16),
    --Access type
    @a_access_type numeric(1))
as
begin
    --Searching for the access rights
    if not exists(select 1 from meta_table_acl_unit_access
        where table_id = @a_table_id
        and user_name = lower(@a_user_name)
        and unit_id = @a_unit_id
        and access_type = @a_access_type)
        --Inserting the unit-level access right
        insert into meta_table_acl_unit_access
        (table_id, user_name, unit_id, access_type)
        values (@a_table_id, lower(@a_user_name), @a_unit_id, @a_access_type);
end;

go

exec dbo.p_drop_proc_if_exists 'p_add_full_access_right';

go

--Function of adding full access rights
create procedure p_add_full_access_right(
    --Table id
    @a_table_id binary(16),
    --User name
    @a_user_name varchar(26),
    --Access type
    @a_access_type numeric(1))
as
begin
    --Searching for the access rights
    if not exists(select 1 from meta_table_acl_full_access
        where table_id = @a_table_id
        and user_name = lower(@a_user_name)
        and access_type = @a_access_type)
        --Inserting access right
        insert into meta_table_acl_full_access (table_id, user_name, access_type)
        values (@a_table_id, lower(@a_user_name), @a_access_type);
end;

go

exec dbo.p_drop_proc_if_exists 'p_add_own_rows_access_right';

go

--Function of adding own rows access right
create procedure p_add_own_rows_access_right(
    --Table id
    @a_table_id binary(16),
    --User name
    @a_user_name varchar(26),
    --Access type
    @a_access_type numeric(1))
as
begin
    --Searching for the access rights
    if not exists(select 1 from meta_table_acl_own_rows
        where table_id = @a_table_id
        and user_name = lower(@a_user_name)
        and access_type = @a_access_type)
        --Inserting access right
        insert into meta_table_acl_own_rows (table_id, user_name, access_type)
        values (@a_table_id, lower(@a_user_name), @a_access_type);
end;

go

exec dbo.p_drop_func_if_exists 'p_get_unit_ids_for_unit_right';

go

--Function of getting a list of units affected by the specified user unit-level access right
create function p_get_unit_ids_for_unit_right(
    --User name
    @a_user_name varchar(26),
    --Access level
    @a_access_level numeric(1))
    returns @a_result table(unit_id binary(16))
as
begin
    declare
    --User unit id
    @a_user_unit_id binary(16),
    --User units cursor
    @a_user_units_cursor cursor;
    
    set @a_user_units_cursor = cursor fast_forward read_only for
    select unit_id
    from meta_user_units
    where user_name = lower(@a_user_name);
    
    open @a_user_units_cursor;
    fetch next from @a_user_units_cursor into @a_user_unit_id;
    
    --Enumerating user units
    while @@fetch_status = 0
    begin    
        --If scope is current unit
        if @a_access_level = 2
        begin
            --Adding the current unit id to the list
            insert into @a_result
            values(@a_user_unit_id);
        end;
        --If scope is current and child units
        else if @a_access_level = 3
        begin
            --Adding current and child units to the list
            insert into @a_result
            select unit_id from dbo.p_get_unit_children(@a_user_unit_id);
        end;
        --If scope is current and parent units
        else if @a_access_level = 4
        begin
            --Adding current and parent units to the list
            insert into @a_result
            select unit_id from dbo.p_get_unit_parents(@a_user_unit_id);
        end;
        --If scope is current, parent and child units
        else if @a_access_level = 5
        begin
            --Adding current, parent and child units to the list
            insert into @a_result
            select unit_id from dbo.p_get_unit_parents_children(@a_user_unit_id);
        end;
        
        fetch next from @a_user_units_cursor into @a_user_unit_id;
    end;
    
    close @a_user_units_cursor;
    deallocate @a_user_units_cursor;
    
    return;
end;

go

exec dbo.p_drop_func_if_exists 'p_users_affected_by_principal';

go

--Function of user affected by the principal
create function p_users_affected_by_principal(
    --Principal name
    @a_principal_name varchar(26))
returns @a_result table(user_name varchar(26))
as
begin
    declare
    --Principal type
    @a_principal_type varchar(64);
    
    --Reading principal type
    select @a_principal_type = principal_type
    from meta_principals
    where principal_name = lower(@a_principal_name);
    
    --If it is a user
    if @a_principal_type = 'user'
    begin
        insert into @a_result values(@a_principal_name);
        return;
    end;
    
    --If it is a role
    
    --Inserting current role users
    insert into @a_result
    select user_name
    from meta_user_roles
    where container_role_name = @a_principal_name;
    
    --Inserting child role users
    with cte_child_roles(role_name) as
    (
        select role_name
        from meta_role_roles
        where container_role_name = @a_principal_name
        union all
        select e.role_name
        from meta_role_roles as e
        inner join cte_child_roles as d on e.container_role_name = d.role_name
    )
    insert into @a_result
    select distinct user_name
    from meta_user_roles
    where container_role_name in (select role_name from cte_child_roles)
    and user_name not in (select user_name from @a_result);
    
    return;
end;

go

exec dbo.p_drop_proc_if_exists 'p_delete_user_acl_matrix';

go

--Function of deleting user acl matrix
create procedure p_delete_user_acl_matrix(
    --Table id
    @a_table_id binary(16),
    --User name
    @a_user_name varchar(26),
    --Access type
    @a_access_type numeric(1))
as
begin
    --Deleting the unit-level access rights
    delete from meta_table_acl_unit_access
    where table_id = @a_table_id
    and user_name = lower(@a_user_name)
    and access_type = @a_access_type;
    
    --Deleting the full access rights
    delete from meta_table_acl_full_access
    where table_id = @a_table_id
    and user_name = lower(@a_user_name)
    and access_type = @a_access_type;
    
    --Deleting the own row access rights
    delete from meta_table_acl_own_rows
    where table_id = @a_table_id
    and user_name = lower(@a_user_name)
    and access_type = @a_access_type;
end;

go

exec dbo.p_drop_proc_if_exists 'p_refresh_user_acl';

go

--Function of refreshing user acl
create procedure p_refresh_user_acl(
    --User name
    @a_user_name varchar(26))
as
begin
    declare
    --Table id
    @a_table_id binary(16),
    --Table cursor
    @a_table_cursor cursor;
    
    set @a_table_cursor = cursor fast_forward read_only for
    select table_id from meta_tables;
    
    open @a_table_cursor;
    fetch next from @a_table_cursor into @a_table_id;

    while @@fetch_status = 0
    begin
        exec dbo.p_refresh_user_acl_matrix @a_table_id, @a_user_name, 1;
        exec dbo.p_refresh_user_acl_matrix @a_table_id, @a_user_name, 2;
        exec dbo.p_refresh_user_acl_matrix @a_table_id, @a_user_name, 3;
        exec dbo.p_refresh_user_acl_matrix @a_table_id, @a_user_name, 4;
        
        fetch next from @a_table_cursor into @a_table_id;
    end;
    
    close @a_table_cursor;
    deallocate @a_table_cursor;
end;

go

exec dbo.p_drop_proc_if_exists 'p_refresh_user_acl_matrix';

go

--Function of refreshing users matrix acl
create procedure p_refresh_user_acl_matrix(
    --Table id
    @a_table_id binary(16),
    --User name
    @a_user_name varchar(26),
    --Access type
    @a_access_type numeric(1))
as
begin
    --Deleting old user acl matrix
    exec dbo.p_delete_user_acl_matrix @a_table_id, @a_user_name, @a_access_type;

    declare
    --Principal name
    @a_principal_name varchar(26),
    --Principal cursor
    @a_principal_cursor cursor;
    
    set @a_principal_cursor = cursor fast_forward read_only for
    select principal_name
    from dbo.p_get_user_principals(@a_user_name);
    
    --Getting all user principals
    open @a_principal_cursor;
    fetch next from @a_principal_cursor into @a_principal_name;
    
    --Updating user acl matrix
    while @@fetch_status = 0
    begin
        exec dbo.p_update_principal_acl_matrix @a_table_id, @a_principal_name;
        
        fetch next from @a_principal_cursor into @a_principal_name;
    end;
    
    close @a_principal_cursor;
    deallocate @a_principal_cursor;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_table_rights';

go

--Function of deleting table access rights
create procedure meta_delete_table_rights(
    --Table id
    @a_table_id binary(16),
    --Principal name
    @a_principal_name varchar(max),
    --Access type
    @a_access_type numeric(1))
with execute as owner
as
begin
    set nocount on;
    declare
    --Table name
    @a_table_name varchar(26),
    --Row count
    @a_row_count numeric,
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_table_exists @a_table_id, @a_result output;
    exec dbo.p_check_principal_exists @a_principal_name, @a_result output;
    
    --Searching for the table rights
    select @a_row_count = count(*)
    from meta_table_acl
    where table_id = @a_table_id
    and principal_name = @a_principal_name
    and access_type = @a_access_type;
    
    if @a_row_count = 0
    begin
        raiserror('Specified table permission was not found (#error_code = 20049)',
        16, 1);
        return;
    end;

    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    if @a_access_type = 1
        exec ('revoke select on ' + @a_table_name + ' from ' + @a_principal_name);
    else if @a_access_type = 2
        exec ('revoke insert on ' + @a_table_name + ' from ' + @a_principal_name);
    else if @a_access_type = 3
        exec ('revoke update on ' + @a_table_name + ' from ' + @a_principal_name);
    else if @a_access_type = 4
        exec ('revoke delete on ' + @a_table_name + ' from ' + @a_principal_name);
    
    --Getting affected by the principal users
    select user_name into #a_affected_user_list
    from dbo.p_users_affected_by_principal(@a_principal_name);
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    delete from meta_table_acl
    where table_id = @a_table_id
    and principal_name = lower(@a_principal_name)
    and access_type = @a_access_type;
    
    --User name
    declare @a_user_name varchar(26),
    --User cursor
    @a_user_cursor cursor;
    
    --Updating users affected by the principal
    set @a_user_cursor = cursor fast_forward read_only for
    select user_name from #a_affected_user_list;
    
    open @a_user_cursor;
    fetch next from @a_user_cursor into @a_user_name;
    
    while @@fetch_status = 0
    begin
        exec dbo.p_refresh_user_acl_matrix @a_table_id, @a_user_name, @a_access_type;		
        fetch next from @a_user_cursor into @a_user_name;
    end;
    
    close @a_user_cursor;
    deallocate @a_user_cursor;
    
    commit;    
end;